I recently inherited an application which has a few issues with referential integrity, unfortunately the existing constraints were not named when they were created so I see lots of this:
ORA-02291: integrity constraint (CCRS_OWNR2.SYS_C00293422) violated - parent key not found
At this point I usually end up yelling something along the lines of ‘What the badgering folly is SYS_C00293422?!?! Why didn’t you name your constraints you massive uberbastards!!!’ at the closest unsuspecting random in the office. I fear this practice is making me unpopular.
So to lessen the damage to my office popularity I set about figuring out how to trace the table from which a constraint originates, based on the constraint name. Turns out it’s pretty easy as Oracle (I’m using 10g) provides a table (or maybe a view?) called all_constraints which describes constraint definitions on tables accessible to the current user.
describe all_constraints;
Name Null Type
------------------------------ -------- ------------
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG()
R_OWNER VARCHAR2(30)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)RELY VARCHAR2(4)
LAST_CHANGE DATEINDEX_OWNER VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)
20 rows selected
We can use this table to identify various properties of a given constraint, based on the constraint name, like in the following example:
select * from all_constraints where owner = '&&schemaName' and constraint_name = '&&constraintName';
One great thing about this method is that it does not require sysdba privileges, this is a major benefit for developers working on locked down schemas as it allows for cracking straight on rather than having to borrow the time of a neighbouring DBA.
This approach is useful in identifying constraint origins however it is frustrating to have to do in the first place, instead I would advocate coming up with a simple naming convention as demonstrated here and using that throughout the Database. This is something that will save the development team time and energy, particularly when your Database grows to tens or hundreds of tables each with several constraints.
Cheers,
EdD