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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s