Oracle查詢表的外來鍵引用關係

需求：用sql語句查詢兩個表之間的外來鍵引用關係

1 進haishu使用者
conn haishu/haishu

2 建立主表
create table jobs
(
job_id varchar2(10) primary key,
job_title varchar2(35),
min_salary number(6),
max_salary number(6)
);


3 建立從表
create table employeenew
(
id number(6),
name varchar2(20),
job_id varchar2(10)
);

4 建立外來鍵
SQL> alter table employeenew add constraint em_jobs_fk foreign key (job_id) references jobs (job_id);

Table altered.

5 檢視外來鍵資訊
SQL> select table_name, constraint_name, constraint_type, r_constraint_name
from user_constraints where table_name = 'EMPLOYEENEW';

TABLE_NAME    CONSTRAINT_NAME CONSTRAINT_TYPE R_CONSTRAINT_NAME
--------------- --------------- --------------- --------------------
EMPLOYEENEW    EM_JOBS_FK    R        SYS_C0011201

這裡SYS_C0011201即為JOBS主鍵的CONSTRAINT_NAME

SQL> select table_name, constraint_name, column_name from user_cons_columns where constraint_name = 'EM_JOBS_FK';

TABLE_NAME    CONSTRAINT_NAME COLUMN_NAME
--------------- --------------- ---------------
EMPLOYEENEW    EM_JOBS_FK    JOB_ID

6 驗證外來鍵約束的作用
SQL> insert into jobs values('IT_DEPT', 'CODER', 1000, 10000);
1 row created.

SQL> insert into employeenew values(101, 'SCOTT', 'IT');
insert into employeenew values(101, 'SCOTT', 'IT')
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.EM_JOBS_FK) violated - parent key not
found

7 查詢表的外來鍵引用關係
select t1.table_name,
       t2.table_name as "TABLE_NAME(R)",
       t1.constraint_name,
       t1.r_constraint_name as "CONSTRAINT_NAME(R)",
       a1.column_name,
       a2.column_name as "COLUMN_NAME(R)"
from user_constraints t1, user_constraints t2, user_cons_columns a1, user_cons_columns a2
where t1.owner = upper('haishu') and
      t1.r_constraint_name = t2.constraint_name and
      t1.constraint_name = a1.constraint_name and
      t1.r_constraint_name = a2.constraint_name;

TABLE_NAME    TABLE_NAME(R)    CONSTRAINT_NAME CONSTRAINT_NAME(R)   COLUMN_NAME     COLUMN_NAME(R)
--------------- --------------- --------------- -------------------- --------------- ---------------
EMPLOYEENEW    JOBS        EM_JOBS_FK    SYS_C0011201         JOB_ID         JOB_ID

注：如果是在當前使用者下，條件t1.owner = upper('haishu')可以不寫。