{"id":347,"date":"2013-11-08T20:18:33","date_gmt":"2013-11-08T12:18:33","guid":{"rendered":"https:\/\/www.techcoil.com\/blog\/?p=347"},"modified":"2018-09-04T13:15:31","modified_gmt":"2018-09-04T05:15:31","slug":"sql-query-to-find-foreign-key-constraints-that-reference-columns-in-a-table-in-oracle-database","status":"publish","type":"post","link":"https:\/\/www.techcoil.com\/blog\/sql-query-to-find-foreign-key-constraints-that-reference-columns-in-a-table-in-oracle-database\/","title":{"rendered":"SQL query to find foreign key constraints that reference columns in a table in oracle database"},"content":{"rendered":"

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<\/code> table. <\/p>\n

As there are foreign key references to the SYSTEMS_CONFIGURATION<\/code> table, I had to first remove all foreign key references to the redundant SYSTEMS_CONFIGURATION<\/code> table row prior to deleting it. <\/p>\n

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

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.<\/p>\n

The USER_CONSTRAINTS and USER_CONS_COLUMNS tables<\/h2>\n

The USER_CONSTRAINTS<\/code> table describes all constraint definitions on tables owned by the current user, while the USER_CONS_COLUMNS<\/code> 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.<\/p>\n

The SQL query to find foreign key constraints that reference columns in the SYSTEMS_CONFIGURATION table<\/h2>\n

After identifying the tables that contain the information which I need, I went on to construct the SQL query.<\/p>\n

\r\nSELECT UC.TABLE_NAME,\r\n\t   UC.R_CONSTRAINT_NAME,\r\n       UCC.TABLE_NAME,\r\n\t   UCC.COLUMN_NAME\r\nFROM USER_CONSTRAINTS  UC,\r\n     USER_CONS_COLUMNS UCC\r\nWHERE UC.R_CONSTRAINT_NAME = UCC.CONSTRAINT_NAME\r\n\t  AND UC.CONSTRAINT_TYPE = 'R'\r\n    AND UCC.TABLE_NAME = 'SYSTEMS_CONFIGURATION';\r\n<\/pre>\n
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<\/code> columns from both tables contain the same value. <\/p>\n
The SQL statement then filters the result by:<\/p>\n