用如下语句查看SCOTT下面表EMP的所有约束和对应的列:
SQL> col CONSTRAINT_NAME for a35
SQL> col COLUMN_NAME for a35
SQL> set linesize 150
SQL> select a.constraint_name, a.column_name,b.constraint_type from all_cons_columns a, all_constraints b
2 where a.constraint_name = b.constraint_name and a.owner='SCOTT'
3 and a.table_name = 'EMP';
CONSTRAINT_NAME COLUMN_NAME C
----------------------------------- ----------------------------------- -
PK_EMP EMPNO P
FK_DEPTNO DEPTNO R
约束类型的定义:
C (check constraint on a table)
P (primary key)
U (unique key)
R (referential integrity)
V (with check option, on a view)
O (with read only, on a view)
查看约束定义,可通过查看表的定义:
select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"