oracle 表外键引用,Oracle查找表的外键引用关系

Oracle查找表的外键引用关系

zhenghaishu

2014-08-27 11:14:02

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