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

calendar Jun 18, 2024 · 3 min read · Oracle Database Administration SQL dba_constraints  ·
Share on: linkedin copy

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

Learn how to use an Oracle SQL query to identify tables referencing a specific table with foreign keys, understand database relationships, and ensure data integrity

Sample SQL Command

 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
 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/

Purpose:

This query serves the purpose of identifying all tables that are dependent on a particular table through foreign key relationships. This information is essential when:

  • Planning database schema changes: You need to understand which tables might be impacted if you modify the referenced table.
  • Troubleshooting data anomalies: Foreign key violations can cause errors, and this query can help you trace the source of such issues.
  • Documenting database relationships: A clear map of foreign key references is valuable for understanding the database structure.

Breakdown of the Code

  1. dba_constraints: This is a system view within Oracle that contains details about all constraints (including foreign keys) in the database.

  2. a.constraint_type = 'R': This condition filters out all constraints except for referential constraints (foreign keys).

  3. a.r_constraint_name = b.constraint_name AND a.r_owner = b.owner: These conditions create a self-join on the dba_constraints view to link the foreign key constraint (a) with the referenced constraint (b).

  4. b.owner = '&table_owner' AND b.table_name = '&table_name': Here's where you specify the owner (schema) and name of the table you want to check for foreign key references. The '&' characters indicate that you'll be prompted to input these values when executing the query.

Key Points and Insights

  • Self-Join: The query uses a self-join on the dba_constraints view to match the foreign key constraints with their corresponding primary/unique key constraints.
  • DBA Privileges: Running this query typically requires privileges on the dba_constraints view. If you don't have them, you might need to use the all_constraints or user_constraints views.
  • Performance: In large databases, this query could take some time. Consider filtering by schema if you know the general location of the tables.

Running the Query

  1. Replace Placeholders: Replace &table_owner and &table_name with the actual schema and table name.
  2. Execute: Run the query in your preferred SQL client.

Example Output

If your referenced table is named CUSTOMER and belongs to the schema SALES, the output might look like:

1OWNER      TABLE_NAME   CONSTRAINT_NAME
2---------- ----------- ------------------------------
3SALES      ORDERS       FK_ORDERS_CUSTOMER
4SALES      INVOICES     FK_INVOICES_CUSTOMER

References

Posts in this series