Finding All Child Tables Referencing a Parent Table in Oracle

Child Tables Referencing a Parent Table in Oracle

• In Oracle databases, understanding foreign key relationships is essential before altering or dropping a parent table. Using the views ALL_CONSTRAINTS and ALL_CONS_COLUMNS, you can identify which child tables reference a specific parent table and the exact columns involved.
• A simple SQL query filtering constraint_type = 'R' (foreign keys) provides this mapping clearly. The output shows child tables, child columns, parent tables, and their constraints. This quick analysis helps avoid errors and ensures safe schema changes.

---

• When working with relational databases, it’s often necessary to understand how tables are related through foreign keys. For example, if you want to drop or modify a parent table, you’ll need to know which child tables reference it.
• In Oracle, you can discover these relationships using the data dictionary views ALL_CONSTRAINTS and ALL_CONS_COLUMNS

SELECT
    a.table_name      AS child_table,
    a.constraint_name AS child_constraint,
    a.column_name     AS child_column,
    c_pk.table_name   AS parent_table,
    c_pk.constraint_name AS parent_constraint,
    c_pk.column_name  AS parent_column
FROM all_cons_columns a
JOIN all_constraints c 
     ON a.owner = c.owner
    AND a.constraint_name = c.constraint_name
JOIN all_cons_columns c_pk 
     ON c.r_owner = c_pk.owner
    AND c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R'  -- 'R' = Foreign Key
  AND c_pk.table_name = UPPER('MST_IRR');