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                             DATE
INDEX_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 constraing, 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