Oracle user_constraints,user_cons_columns 查看外键

最新推荐文章于 2022-12-23 11:40:53 发布

原创最新推荐文章于 2022-12-23 11:40:53 发布 · 1.1k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #C #C++ #C# #SQL

DB 专栏收录该内容

28 篇文章

订阅专栏

本文提供了一种在Oracle数据库中查询指定表主键和外键约束关系的方法。通过使用特定的SQL语句，可以获取到表的主键、外键及其对应列的信息。此方法有助于理解表之间的关联。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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