Oracle 查看一个表对应的主键和外键的约束关系,查看的语句:
数据字典表列说明:
select a.owner, --主键拥有者
a.table_name, --主键表
b.column_name, --主键列
c.OWNER, --外键拥有者
c.table_name, --外键表
d.column_name --外键列
from user_constraints a
left join user_cons_columns b
on a.constraint_name=b.constraint_name
left join user_constraints c
on c.R_CONSTRAINT_NAME=a.constraint_name
left join user_cons_columns d
on c.constraint_name=d.constraint_name
where a.constraint_type='P'
and a.table_name='xxx' --需要查看主外键关系的表
order by a.table_name
数据字典表列说明:
desc user_constraints
Name Comments
OWNER 表的所有者
CONSTRAINT_NAME 约束名
CONSTRAINT_TYPE 约束类型
TABLE_NAME Name associated with table with constraint definition
SEARCH_CONDITION Text of search condition for table check
R_OWNER Owner of table used in referential constraint
R_CONSTRAINT_NAME Name of unique constraint definition for referenced table
DELETE_RULE The delete rule for a referential constraint
STATUS Enforcement status of constraint - ENABLED or DISABLED
DEFERRABLE Is the constraint deferrable - DEFERRABLE or NOT DEFERRABLE
DEFERRED Is the constraint deferred by default - DEFERRED or IMMEDIATE
VALIDATED Was this constraint system validated? - VALIDATED or NOT VALIDATED
GENERATED Was the constraint name system generated? - GENERATED NAME or USERNAME
BAD Creating this constraint should give ORA-02436. Rewrite it before2000 AD.
RELY If set, this flag will be used in optimizer
LAST_CHANGE The date when this column was last enabled or disabled
INDEX_OWNER The owner of the index used by the constraint
INDEX_NAME The index used by the constraint
INVALID
VIEW_RELATED
desc user_cons_columns;
Name Default Comments
OWNER Owner of the constraint definition
CONSTRAINT_NAME Name associated with the constraint definition
TABLE_NAME Name associated with table with constraint definition
COLUMN_NAME Name associated with column or attribute of object column specified in the constraint definition
POSITION Original position of column or attribute in definition