Ozzie.eu

Getting Foreign Keys on Oracle

When maintaining a live system, sometimes one must undo a committed transaction on the database. In other words, delete some records and their trails.

A difficulty that sometimes arises on complex relational models, with cascade deletion disabled, is to find out what records on other data tables are pointing to the ones I want to erase.

On Oracle, to find out which tables have a foreign key that points to the primary key of the records we want to delete, one just issue the command:

select * from ALL_CONSTRAINTS where R_CONSTRAINT_NAME = '[Your PK constraint name]'

This statement returns as a result the tables that have a foreign key constraint pointing to the targeted table's primary key.