SQL> desc dict 名称 是否为空? 类型 ----------------------------------------- -------- ----------- TABLE_NAME VARCHAR2(30 COMMENTS VARCHAR2(40SQL> select * from dict where instr(comments,'constr')>0;TABLE_NAME ------------------------------ COMMENTS --------------------------------------------------------------- ALL_CONS_COLUMNS Information about accessible columns in constraint definitionsDBA_CONS_COLUMNS Information about accessible columns in constraint definitionsUSER_CONS_COLUMNS Information about accessible columns in constraint definitions 找到外鍵: SELECT a.r_constraint_name,b.column_name from user_constraints a, user_cons_columns b where a.table_name = 'your_table' and a.constraint_type='R' and a.constraint_name=b.constraint_name
select * from user_constraints WHERE R_CONSTRAINT_NAME='FK_CON';
jiezhi: 你的方法只能找到外键的名称,相关表明但不能找到该外键关联那个表,对于以下外键 alter table TEST add constraint FP foreign key (child) references TEST1 (parent); 我想得到外键的 Test1,以及parent信息。而不仅仅是test和child信息。 用user_cons_columns ,user_constraints 只能找到test和child
select a.OWNER 用户名, a.TABLE_NAME 表名, b.COLUMN_NAME 列名 from USER_CONSTRAINTS a,USER_CONS_COLUMNS b where a.CONSTRAINT_NAME=(select R_CONSTRAINT_NAME from USER_CONSTRAINTS where USER_CONSTRAINTS.CONSTRAINT_NAME='CHILD') and a.CONSTRAINT_NAME=b.CONSTRAINT_NAME /
-- 查询外键约束(查某表的所有父表) select c.constraint_name,cc.column_name,rcc.owner,rcc.table_name,rcc.column_name from user_constraints c,user_cons_columns cc,user_cons_columns rcc where c.owner='SALIEN_SBGL' and c.table_name='JH_NDGXLGJH_TB' and c.constraint_type='R' and c.owner=cc.owner and c.constraint_name=cc.constraint_name and c.r_owner=rcc.owner and c.r_constraint_name=rcc.constraint_name and cc.position=rcc.position order by c.constraint_name,cc.position;--查询连接到某表的所有外键(查某表的所有子表) select rcc.owner,rcc.table_name,rcc.constraint_name,rcc.column_name,c.table_name,c.constraint_name,cc.column_name from user_constraints c,user_cons_columns cc,user_cons_columns rcc where lower(c.owner)='d7i' and rcc.table_name='R5HOME' and c.constraint_type='R' and c.owner=cc.owner and c.constraint_name=cc.constraint_name and c.r_owner=rcc.owner and c.r_constraint_name=rcc.constraint_name and cc.position=rcc.position order by c.constraint_name,cc.position-- 查询主键唯一键约束 select c.constraint_name,c.constraint_type,cc.column_name from user_constraints c,user_cons_columns cc where c.owner='SALIEN_SBGL' and c.table_name='JH_NDGXLGJH_TB' and c.owner=cc.owner and c.constraint_name=cc.constraint_name and c.constraint_type in ('P','U') order by c.constraint_type,c.constraint_name,cc.position;
名称 是否为空? 类型
----------------------------------------- -------- -----------
TABLE_NAME VARCHAR2(30
COMMENTS VARCHAR2(40SQL> select * from dict where instr(comments,'constr')>0;TABLE_NAME
------------------------------
COMMENTS
---------------------------------------------------------------
ALL_CONS_COLUMNS
Information about accessible columns in constraint definitionsDBA_CONS_COLUMNS
Information about accessible columns in constraint definitionsUSER_CONS_COLUMNS
Information about accessible columns in constraint definitions
找到外鍵:
SELECT a.r_constraint_name,b.column_name
from user_constraints a, user_cons_columns b
where a.table_name = 'your_table' and a.constraint_type='R'
and a.constraint_name=b.constraint_name
你的方法只能找到外键的名称,相关表明但不能找到该外键关联那个表,对于以下外键
alter table TEST
add constraint FP foreign key (child)
references TEST1 (parent);
我想得到外键的 Test1,以及parent信息。而不仅仅是test和child信息。
用user_cons_columns ,user_constraints 只能找到test和child
a.TABLE_NAME 表名,
b.COLUMN_NAME 列名
from USER_CONSTRAINTS a,USER_CONS_COLUMNS b
where a.CONSTRAINT_NAME=(select R_CONSTRAINT_NAME from USER_CONSTRAINTS
where USER_CONSTRAINTS.CONSTRAINT_NAME='CHILD')
and a.CONSTRAINT_NAME=b.CONSTRAINT_NAME
/
-- 查询外键约束(查某表的所有父表)
select c.constraint_name,cc.column_name,rcc.owner,rcc.table_name,rcc.column_name
from user_constraints c,user_cons_columns cc,user_cons_columns rcc
where c.owner='SALIEN_SBGL'
and c.table_name='JH_NDGXLGJH_TB'
and c.constraint_type='R'
and c.owner=cc.owner
and c.constraint_name=cc.constraint_name
and c.r_owner=rcc.owner
and c.r_constraint_name=rcc.constraint_name
and cc.position=rcc.position
order by c.constraint_name,cc.position;--查询连接到某表的所有外键(查某表的所有子表)
select rcc.owner,rcc.table_name,rcc.constraint_name,rcc.column_name,c.table_name,c.constraint_name,cc.column_name
from user_constraints c,user_cons_columns cc,user_cons_columns rcc
where lower(c.owner)='d7i'
and rcc.table_name='R5HOME'
and c.constraint_type='R'
and c.owner=cc.owner
and c.constraint_name=cc.constraint_name
and c.r_owner=rcc.owner
and c.r_constraint_name=rcc.constraint_name
and cc.position=rcc.position
order by c.constraint_name,cc.position-- 查询主键唯一键约束
select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.owner='SALIEN_SBGL'
and c.table_name='JH_NDGXLGJH_TB'
and c.owner=cc.owner
and c.constraint_name=cc.constraint_name
and c.constraint_type in ('P','U')
order by c.constraint_type,c.constraint_name,cc.position;