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
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
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_TYPEcolumn. A 'R' in the
CONSTRAINT_TYPEcolumn 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