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/