Debugging Database Constraint Errors

Posted by Hariharan Vadivelu on
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.

4 comments:

  1. Well explained. Got to learn new things from your Blog on Coded UI.Coded UI Training in Chennai

    ReplyDelete
  2. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in TECHNOLOGY , kindly Contact MaxMunus
    MaxMunus Offer World Class Virtual Instructor led training on TECHNOLOGY. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 1,00,000 + trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Saurabh srivastava
    MaxMunus
    E-mail: saurabh@maxmunus.com
    Skype id: saurabhmaxmunus
    Ph:+918553576305
    www.MaxMunus.com


    ReplyDelete