Sunday, May 27, 2012

Finding table for Oracle constraint violation

When Oracle reports a constraint violation, you might get something unhelpfully cryptic like:

ORA-00001: unique constraint (FOO.SYS_C003567231) violated

The constraint is system-generated, based on UNIQUE being specified in the DDL, so the name is unhelpful. With no other information, finding the affected table can be difficult. You can query for the table with the following SQL:

select constraint_name, table_name
from user_constraints
where constraint_name='SYS_C003567231

This is a handy query to know, so I'm noting it here for my future reference. It's also described in a number of other blogs like this one.

1 comment: