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;
REFERENCING_COLUMN REFERENCED_COLUMN
------------------------------ ------------------------------
HR.DEPARTMENTS.MANAGER_ID HR.EMPLOYEES.EMPLOYEE_ID
HR.EMPLOYEES.MANAGER_ID HR.EMPLOYEES.EMPLOYEE_ID
HR.JOB_HISTORY.EMPLOYEE_ID HR.EMPLOYEES.EMPLOYEE_ID
OE.CUSTOMERS.ACCOUNT_MGR_ID HR.EMPLOYEES.EMPLOYEE_ID
OE.ORDERS.SALES_REP_ID HR.EMPLOYEES.EMPLOYEE_ID
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.