user_constraints,user_cons_columns查看外键
来源:互联网 发布:3.35物品数据库炉石 编辑:程序博客网 时间:2024/06/08 10:41
来源:http://blog.csdn.net/maqinqin/archive/2009/05/26/4217045.aspx
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
另外的一种写法
select
a.owner 外键拥有者,
a.table_name 外键表,
substr(c.column_name,1,127) 外键列,
b.owner 主键拥有者,
b.table_name 主键表,
substr(d.column_name,1,127) 主键列
from
user_constraints a,
user_constraints b,
user_cons_columns c,
user_cons_columns d
where
a.r_constraint_name=b.constraint_name
and a.constraint_type='R'
and b.constraint_type='P'
and a.r_owner=b.owner
and a.constraint_name=c.constraint_name
and b.constraint_name=d.constraint_name
and a.owner=c.owner
and a.table_name=c.table_name
and b.owner=d.owner
and b.table_name=d.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
desc后发现
user_constraints是表约束的视图,描述的是约束类型(constraint_type)是什么,属于哪些表(table_name),如果约束的类型为R(外键)的话,那么r_constraint_name字段存放的就是被引用主表中的主键约束名。
user_cons_columns是表约束字段的视图,说明表中的和约束相关的列参与了哪些约束。这些约束有主键约束,外键约束,索引约束.
两者可以通过(owner,constraint_name,table_name)关联
- user_constraints,user_cons_columns查看外键
- user_constraints,user_cons_columns查看外键
- 查看表中有哪些完整性约束(user_constraints 和 user_cons_columns)
- user_constraints与user_cons_columns cu视图
- 如何查看user_constraints里的search_condiction
- 约束:USER_CONSTRAINTS
- 约束列 USER_CONS_COLUMNS
- user_constraints表字段Constraint_type值含义
- oracle查看外键
- 查看外键父子关系
- 添加、查看、删除外键
- sql server查看外键。
- 查看、清除数据库中的外键约束
- mysql 查看 外键的依赖关系
- 查看数据库中所有的外键
- 查看未创建索引的外键
- postgres查看外键的关联关系
- 查看SqlServer2000的外键列表
- Makefile‘混和的隐含和普通规则’错误的解决
- 利用java反射机制生成entity的一个例子
- Css高级语法
- 谢谢大家
- Jqurey 控件及其插件下载地址
- user_constraints,user_cons_columns查看外键
- Oracle 用户 profile 属性
- 深入理解 Android Activity的生命周期
- 进程同步与互斥
- OEM 应用程序要求的数据库权限超出了您当前具有的权限 解决方法
- 进程的同步与互斥
- 【Android】修改Activity的标题样式
- oracle 解锁
- WinCE5文件的路径怎么表示?