Menu
I plan to delete data from a table, I would like to know how many and which tables have a foreign key reference to this particular table in Oracle. As I will have to set the foreign keys to null. I would like to know list of all tables which have a FK to this particular table.
802
You can check for foreign key constraint errors by looking at the dba_constraints and the dba_cons_columns views. Using the cascade constraints clause in a drop table will force a cascade delete to occur in all child tables. Oracle also has the drop table if exists feature.
First method is with table Constraints tab (select table and select Constraints tab). Tab lists table constraints - primary, unique and foreign keys and check constraints - all in one grid. Foreign keys are the ones with 'Foreign_Key' value in CONSTRAINT_TYPE column.
The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.
SELECT
FK.OWNER||'.'||FK.TABLE_NAME AS CHILD_TABLE,
SRC.OWNER||'.'||SRC.TABLE_NAME AS PARENT_TABLE,
FK.CONSTRAINT_NAME AS FK_CONSTRAINT,
SRC.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT
FROM ALL_CONSTRAINTS FK
JOIN ALL_CONSTRAINTS SRC ON FK.R_CONSTRAINT_NAME = SRC.CONSTRAINT_NAME
WHERE
FK.CONSTRAINT_TYPE = 'R'
AND SRC.OWNER = 'MY_SCHEMA'
AND SRC.TABLE_NAME = 'MY_TABLE';
I have a situation where the table I'm interested in isn't owned by the schema I was connecting as. So I needed to modify the query in the currently accepted answer to use ALL_CONSTRAINTS instead of USER_CONSTRAINTS. In the process, I made a mistake, and I found the accepted answer to be very difficult to read so that I could fix it. (The lack of explanation didn't help.) As a result, I ended up coming up with my own query. It's basically the same, but I think it's a bit easier to grok.
FK.CONSTRAINT_TYPE = 'R' filters down FK to a set of foreign key constraints, and the join pairs these foreign keys up with their "Referenced constraint". (The referenced constraint is usually the primary key of the "parent" table.) Finally, we filter down to the parent table we're interested in using SRC.OWNER = 'MY_SCHEMA' AND SRC.TABLE_NAME = 'MY_TABLE'.
Naturally, you can switch this to use USER_CONSTRAINTS if you wish; just remove the SRC.OWNER check and the OWNER prefixes in the SELECT.
166
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
