SQL query to find foreign key constraints that reference columns in a table in oracle database

I was rather new to a system with hundreds of Oracle database tables. There was once when I realized that I had to delete a redundant row from the SYSTEMS_CONFIGURATION table.

As there are foreign key references to the SYSTEMS_CONFIGURATION table, I had to first remove all foreign key references to the redundant SYSTEMS_CONFIGURATION table row prior to deleting it.

To be able to do so, I had to identify the tables which could have rows referencing that redundant SYSTEMS_CONFIGURATION table. But how?

This post describes the SQL query which I had ran on my Oracle database to find all the tables which had foreign key references to SYSTEMS_CONFIGURATION table.

The USER_CONSTRAINTS and USER_CONS_COLUMNS tables

The USER_CONSTRAINTS table describes all constraint definitions on tables owned by the current user, while the USER_CONS_COLUMNS table describes columns that are owned by the current user and that are specified in constraint definitions. In other words, we can get information about foreign key columns from USER_CONSTRAINTS and the columns being referenced from the USER_CONS_COLUMNS table.

The SQL query to find foreign key constraints that reference columns in the SYSTEMS_CONFIGURATION table

After identifying the tables that contain the information which I need, I went on to construct the SQL query.

SELECT UC.TABLE_NAME,
	   UC.R_CONSTRAINT_NAME,
       UCC.TABLE_NAME,
	   UCC.COLUMN_NAME
FROM USER_CONSTRAINTS  UC,
     USER_CONS_COLUMNS UCC
WHERE UC.R_CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
	  AND UC.CONSTRAINT_TYPE = 'R'
    AND UCC.TABLE_NAME = 'SYSTEMS_CONFIGURATION';

The SQL statement performs a join on rows returned from both the USER_CONSTRAINTS and USER_CONS_COLUMNS tables, on condition that the CONSTRAINT_NAME columns from both tables contain the same value.

The SQL statement then filters the result by:

  • matching the rows having 'R' in the CONSTRAINT_TYPE column. A 'R' in the CONSTRAINT_TYPE column indicates that the particular constraint is a referential constraint; which enforces foreign key relationship between the tables.
  • matching the rows having 'SYSTEMS_CONFIGURATION' in the TABLE_NAME column.

About Clivant

Clivant a.k.a Chai Heng enjoys composing software and building systems to serve people. He owns techcoil.com and hopes that whatever he had written and built so far had benefited people. All views expressed belongs to him and are not representative of the company that he works/worked for.