desc 后发现
user_constraints是表约束的视图,描述的是约束类型(constraint_type)是什么,属于哪些表(table_name),如果约束的类型为R(外键)的话,那么r_constraint_name字段存放的就是被引用主表中的主键约束名。
user_cons_columns是表约束字段的视图,说明表中的和约束相关的列参与了哪些约束。这些约束有主键约束,外键约束,索引约束.
两者可以通过(owner,constraint_name,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 Owner of the table
CONSTRAINT_NAME Name associated with constraint definition
CONSTRAINT_TYPE Type of constraint definition
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 USER NAME
BAD Creating this constraint should give ORA-02436. Rewrite it before 2000 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 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
posted on 2009-07-30 16:53
月光记忆 阅读(10027)
评论(1) 编辑 收藏 所属分类:
ORCLE