Skip to content
Home » Oracle » How to Find References to Table

How to Find References to Table

Referential Integrity

To check the referential relationship of a table in details, we have to join some dictionary views, ALL_CONSTRAINTS and ALL_CONS_COLUMNS.

For example, we'd like to know the referenced and referencing columns of table HR.EMPLOYEES, we can do this to find what references to column:

SQL> column referencing_column format a30;
SQL> column referenced_column format a30;
SQL> select a.owner || '.' || a.table_name || '.' || b1.column_name referencing_column, b2.owner || '.' || b2.table_name || '.' ||  b2.column_name referenced_column from all_constraints a, all_cons_columns b1, all_cons_columns b2 where a.constraint_name = b1.constraint_name and a.r_constraint_name = b2.constraint_name and a.r_constraint_name in (select constraint_name from all_constraints where owner = 'HR' and table_name = 'EMPLOYEES') order by 1,2;

------------------------------ ------------------------------

As we can see, there're 5 pairs of referential relationship about the table in this case. In which, the second pair is a self-referential integrity constraint.

It's pretty important to know the referential relationship in order to disable foreign key constraints before data migration.

For your own needs, please replace OWNER and TABLE_NAME with yours.

Leave a Reply

Your email address will not be published. Required fields are marked *