oracle查看约束条件,plsql有约束条件提示，怎么查这个约束条件在哪个table上

SQL code主键约束

SELECT

USER_CONS_COLUMNS.CONSTRAINT_NAME AS 约束名,

USER_CONS_COLUMNS.TABLE_NAME AS 表名,

USER_CONS_COLUMNS.COLUMN_NAME AS 列名,

USER_CONS_COLUMNS.POSITION AS 位置

FROM

USER_CONSTRAINTS

JOIN USER_CONS_COLUMNS

ON (USER_CONSTRAINTS.CONSTRAINT_NAME

= USER_CONS_COLUMNS.CONSTRAINT_NAME)

WHERE

CONSTRAINT_TYPE = 'P';

外键约束

SELECT

USER_CONS_COLUMNS.CONSTRAINT_NAME AS 约束名,

USER_CONS_COLUMNS.TABLE_NAME AS 子表名,

USER_CONS_COLUMNS.COLUMN_NAME AS 子表列名,

USER_CONS_COLUMNS.POSITION AS 位置,

USER_INDEXES.TABLE_NAME AS 主表名,

USER_IND_COLUMNS.COLUMN_NAME AS 主表列名

FROM

USER_CONSTRAINTS

JOIN USER_CONS_COLUMNS

ON (USER_CONSTRAINTS.CONSTRAINT_NAME

= USER_CONS_COLUMNS.CONSTRAINT_NAME)

JOIN USER_INDEXES

ON (USER_CONSTRAINTS.R_CONSTRAINT_NAME

= USER_INDEXES.INDEX_NAME)

JOIN USER_IND_COLUMNS

ON (USER_INDEXES.INDEX_NAME = USER_IND_COLUMNS.INDEX_NAME)

WHERE

CONSTRAINT_TYPE = 'R';

其他约束

SELECT

USER_CONS_COLUMNS.CONSTRAINT_NAME AS 约束名,

USER_CONS_COLUMNS.TABLE_NAME AS 表名,

USER_CONS_COLUMNS.COLUMN_NAME AS 列名,

USER_CONS_COLUMNS.POSITION AS 位置,

CONSTRAINT_TYPE,

SEARCH_CONDITION

FROM

USER_CONSTRAINTS

JOIN USER_CONS_COLUMNS

ON (USER_CONSTRAINTS.CONSTRAINT_NAME

= USER_CONS_COLUMNS.CONSTRAINT_NAME)

WHERE

CONSTRAINT_TYPE IN ('C', 'V', 'O');

------解决方案--------------------

用scott 用户举个例子如下SQL codeSQL> show user

USER 为 "SCOTT"

SQL> ed

已写入 file afiedt.buf

1 select a.constraint_name 外键名, a.table_name 外键表, b.constraint_name 主

键名, b.table_name 关联主键表

2 from user_constraints a, user_constraints b

3* where b.table_name=upper('dept') and a.constraint_type = 'R' and b.constr

aint_type = 'P' and a.r_constraint_name = b.constraint_name

SQL> /

外键名 外键表 主键名

------------------------------ ------------------------------ ------------------

------------

关联主键表

------------------------------

FK_DEPTNO EMP PK_DEPT

DEPT