It is quite common that you might notice database costraint errors in your FFDC or SystemOut logs, the message could be a bit cryptic to understand, in this blog I will provide few basic steps to debug the constraint related errors. The steps are specifically for Oracle DB and might differ based on your database type.
Sample error you might notice in your ffdc logs, as you can see from the error message it is difficult to determine which table/column violated the constraint.
Chained exceptions:java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (WCSADM.SYS_C00147213) violated
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:85)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:693)
at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:617)
Run following sql against the database application is connected to.
select * from dba_cons_columns where constraint_name='SYS_C00147213' and owner ='<schema_name>'
This should give following output.
owner, constraint_name, table_name, column_name,position
<schema name>, SYS_C00147213, DMUSERBHVR, PERSONALIZATIONID, 1
Based on this result we can conclude that there was a unique key violation for PERSONALIZATIONID column on table DMUSERBHVR
Another example to check all constraints defined on a table
select * from dba_constraints where table_name='USERS'
and owner='<Schema Name>'
This should output all constraints defined on Users table.
Sample error you might notice in your ffdc logs, as you can see from the error message it is difficult to determine which table/column violated the constraint.
Chained exceptions:java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (WCSADM.SYS_C00147213) violated
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:85)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:693)
at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:617)
Run following sql against the database application is connected to.
select * from dba_cons_columns where constraint_name='SYS_C00147213' and owner ='<schema_name>'
This should give following output.
owner, constraint_name, table_name, column_name,position
<schema name>, SYS_C00147213, DMUSERBHVR, PERSONALIZATIONID, 1
Based on this result we can conclude that there was a unique key violation for PERSONALIZATIONID column on table DMUSERBHVR
Another example to check all constraints defined on a table
select * from dba_constraints where table_name='USERS'
and owner='<Schema Name>'
This should output all constraints defined on Users table.
