Oracle: List Tables Using a Table as a Foreign Key

calendar Jun 18, 2024 / · 6 min read · Oracle Database Administration SQL dba_constraints all_constraints user_constraints  ·
Share on: linkedin copy

List Tables That Are Using the Specified Table as a Foreign Key using dba_constraints

Which tables point at SALES.CUSTOMER? You need the answer before you truncate it, drop it, reload it or change its primary key, because every child table with a foreign key to it will either block the change or be affected by it.

Purpose

This script lists every foreign key in the database that references a given parent table, with the owner and name of the child table and the constraint. A second version writes ALTER TABLE ... DISABLE CONSTRAINT statements for the same foreign keys, which is the usual first step before a bulk reload of the parent.

The query works by joining dba_constraints to itself. A foreign key row records the name and owner of the constraint it refers to, so matching those to the parent table's primary or unique key constraint finds every child.

Code

 1set lines 100 pages 999
 2select	a.owner
 3,	a.table_name
 4,	a.constraint_name
 5from	dba_constraints a
 6,	dba_constraints b
 7where	a.constraint_type = 'R'
 8and 	a.r_constraint_name = b.constraint_name
 9and 	a.r_owner  = b.owner
10and 	b.owner = '&table_owner'
11and	b.table_name = '&table_name'
12/

Same as above, but produces disable constraint statements:

 1set lines 100 pages 999
 2col discon format a100
 3select 'alter table '||a.owner||'.'||a.table_name||' disable constraint '||a.constraint_name||';' discon
 4from	dba_constraints a
 5,	dba_constraints b
 6where	a.constraint_type = 'R'
 7and 	a.r_constraint_name = b.constraint_name
 8and	a.r_owner  = b.owner
 9and 	b.owner = '&table_owner'
10and	b.table_name = '&table_name'
11/

Code Breakdown

Two copies of the same view

dba_constraints appears twice. Alias a is the child side: the foreign key constraints. Alias b is the parent side: the primary or unique key that each foreign key refers to.

The foreign key filter

a.constraint_type = 'R' keeps only referential integrity constraints. Oracle's reference lists the constraint types as C (check), P (primary key), U (unique key), R (referential integrity), V and O (view constraints), and a few newer codes; R is the foreign key.

The join

a.r_constraint_name = b.constraint_name and a.r_owner = b.owner connect each foreign key to the constraint it references. Oracle documents R_OWNER as the owner of the table referred to and R_CONSTRAINT_NAME as the name of the unique constraint definition for the referenced table. Joining on both the name and the owner matters: two schemas can each have a constraint called PK_CUSTOMER.

The parent table

b.owner = '&table_owner' and b.table_name = '&table_name' pick the parent. The & makes SQL*Plus prompt for each value. Enter them in upper case unless the table was created with a quoted mixed-case name, because the dictionary stores names that way.

The generated statements

The second query builds text rather than changing anything. Each row is one ALTER TABLE owner.table DISABLE CONSTRAINT name; statement. Spool the output to a file, check it, then run it. col discon format a100 widens the column so long statements do not wrap.

Key Points

  • dba_constraints needs privileges. Without access to the DBA_ views, use all_constraints (constraints on tables you can access) or user_constraints (your own schema), which have the same columns used here.
  • Child tables can be in other schemas. The query returns a.owner because a foreign key in another schema can reference this table.
  • The delete rule matters. dba_constraints.delete_rule shows CASCADE, SET NULL or NO ACTION for each foreign key. A cascading foreign key means deleting parent rows also deletes child rows.
  • Disabling is not dropping. A disabled constraint keeps its definition and can be enabled again; enabling it later checks the existing rows unless you choose otherwise.

Insights and Best Practices

Add the delete rule and status to the output

For a change review, it helps to see how each child behaves. Adding a.delete_rule and a.status to the select list shows which foreign keys cascade and which are already disabled, in the same run.

 1select	a.owner
 2,	a.table_name
 3,	a.constraint_name
 4,	a.delete_rule
 5,	a.status
 6from	dba_constraints a
 7,	dba_constraints b
 8where	a.constraint_type = 'R'
 9and 	a.r_constraint_name = b.constraint_name
10and 	a.r_owner  = b.owner
11and 	b.owner = '&table_owner'
12and	b.table_name = '&table_name'
13order by a.owner, a.table_name
14/

A row with DELETE_RULE = CASCADE deserves a second look before any bulk delete on the parent, because the delete will reach into that child table too. A row with STATUS = DISABLED is a foreign key that is not being enforced today; find out why before you enable it, since existing rows may already break the rule.

Pair the disable script with an enable script

Generate the matching enable constraint statements before you run the disable ones, by changing the literal text in the second query. Keep both files together, so the reload can be undone in the same order. When you enable the foreign keys after the reload, the default is VALIDATE, which checks every existing child row against the parent. Oracle's SQL reference notes that ENABLE NOVALIDATE only makes new DML comply and does not ensure that existing data complies, so use it only when you have checked the data another way.

Check the child foreign key columns are indexed

Oracle's Concepts guide gives the example of a parent departments table and child employees table: when a department is deleted and employees.department_id is not indexed, "the database must scan employees to see if any employees exist in department 10." On large child tables, an unindexed foreign key turns every parent delete into a full scan of the child and affects locking as well. The list this script produces is the list of child tables to check for an index on the foreign key columns.

Expect more than one level

A child table can itself be a parent. Run the script again for each child it returns to find grandchildren before a large reload or delete.

When to Run This

  • Before truncating, dropping or reloading a parent table.
  • Before changing a parent table's primary key.
  • When a delete on a parent table runs slowly or waits on locks, to find child tables with unindexed foreign keys.
  • When documenting a schema's relationships for a migration.

Troubleshooting Common Issues

If the query returns no rows, check the case of the owner and table name first; customer will not match a table stored as CUSTOMER. Then confirm the parent has a primary or unique key at all: a foreign key can only reference one, so a table without one has no children. If you see ORA-00942: table or view does not exist, you lack access to dba_constraints; switch to all_constraints. If a disable statement fails, read the full error text before retrying: the usual causes are a missing ALTER privilege on the child table or a typing error in a hand-edited statement.

References

Posts in this series