Wednesday, 13 May 2009

Finding constraints in Oracle

It can be quite frustrating when you get exceptions like those below - constraint violations where the constraint has a system generated name which means nothing to you:

2009-03-23 23:30:43 ERROR [AbstractFlushingEventListener.performExecutions] Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (SAMPLE.SYS_C00123456) violated

The easiest way to find out which table this constraint is associated with is to issue a statement as shown:
select constraint_name, table_name from user_constraints where constraint_name='SYS_C00123456';

Now you know the table involved, you can easily find out the exact nature of the constraint in various ways - my tool of choice is SqlDeveloper - a GUI interface to Oracle (and other) database.