Oracle: Referential integrity

Error messages related to referential integrity include:

• ORA-02266: unique/primary keys in table referenced by enabled foreign keys
• ORA-02273: this unique/primary key is referenced by some foreign keys
• ORA-02429: cannot drop index used for enforcement of unique/primary key
• ORA-02449: unique/primary keys in table referenced by foreign keys
• ORA-02291: integrity constraint … violated - parent key not found
• ORA-02292: integrity constraint (…) violated - child record found

In case of such an error, this SQL statement (which uses all_constraints)) might help find primary-foreign key relationships.

Select statements to

• find primary-foreign key relations
• recursively query referential integrity dependencies
• create a graphiviz file showing referential constraint dependencies

Trying to delete a record in a table with a primary key whose value is still referenced in a tablee with a foreign key causes Oracle to throw ORA-02292: integrity constraint (…) violated - child record found.

Similarly, trying to insert a record into a table with a foreign key whose value is not in the referenced table causes Oracle to throw ORA-02291: integrity constraint … violated - parent key not found.

Referential integrity from a foreign key to a unique constraint