Oracle查找表的外键引用关系

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')可以不写。
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29485627/viewspace-1258689/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29485627/viewspace-1258689/