Oracle PL/SQL Tutorial/System Tables Data Dictionary/user cons columns
Combine user_constraints and user_cons_columns
<source lang="sql">
SQL> SQL> SQL> select a.constraint_type || a.constraint_name as csort,
2 decode(a.constraint_type,"P","Primary Key","R","Foreign Key","C","Check","Other") as ctype, 3 a.table_name || "." || a.constraint_name as cname, 4 b.position as cpos, 5 b.column_name as colname, 6 a.search_condition as crule, 7 a.r_constraint_name as crefer 8 from user_constraints a, user_cons_columns b 9 where a.constraint_name = b.constraint_name and rownum < 50 10 order by csort, b.position 11 /
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CBIN$7jo86iFsTs26s2bB3k3wxA==$0 Check BIN$z+/2/T9WTRWmOfXZhvGBPQ==$0.BIN$7jo86iFsTs26s2bB3k3wxA==$0 WORKER_ID CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"WORKER_ID" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CBIN$AG2pRJeXSS2fZBgGOwm+Zw==$0 Check BIN$BIALfQCESHa/SipQl+oqGg==$0.BIN$AG2pRJeXSS2fZBgGOwm+Zw==$0 ID CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"ID" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CBIN$Hp7fY/C7QHuGuuejsVe0Kg==$0 Check BIN$BIALfQCESHa/SipQl+oqGg==$0.BIN$Hp7fY/C7QHuGuuejsVe0Kg==$0 LOGICAL_WORKPLACE_ID CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"LOGICAL_WORKPLACE_ID" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CBIN$LK1awudKQBShT/ikiTUW+Q==$0 Check BIN$uR9LzoavTbyjK4b4t8kgXg==$0.BIN$LK1awudKQBShT/ikiTUW+Q==$0 ACTIVE_DATE CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"ACTIVE_DATE" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CBIN$Q858xZXjRjaShEoGqYOlpg==$0 Check BIN$z+/2/T9WTRWmOfXZhvGBPQ==$0.BIN$Q858xZXjRjaShEoGqYOlpg==$0 ID CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"ID" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CBIN$QgU3IiZvTlSc5soFs0Yprg==$0 Check BIN$ukEn3nqRT+u/paBe8fJpxQ==$0.BIN$QgU3IiZvTlSc5soFs0Yprg==$0 ACTIVE_DATE CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"ACTIVE_DATE" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CBIN$hCmUWcCnTVedz74x69dwOw==$0 Check BIN$z+/2/T9WTRWmOfXZhvGBPQ==$0.BIN$hCmUWcCnTVedz74x69dwOw==$0 ACTIVE_DATE CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"ACTIVE_DATE" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CBIN$hdsDtlRrQyiRZwUDoifzYQ==$0 Check BIN$BIALfQCESHa/SipQl+oqGg==$0.BIN$hdsDtlRrQyiRZwUDoifzYQ==$0 WORKER_ID CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"WORKER_ID" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CBIN$ipIeQQ+8SWqoL5m5AZhtYw==$0 Check BIN$ukEn3nqRT+u/paBe8fJpxQ==$0.BIN$ipIeQQ+8SWqoL5m5AZhtYw==$0 WORKER_ID CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"WORKER_ID" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CBIN$ixWMQi5sQuS6TWePjKts3g==$0 Check BIN$2AYTRnCPRfG80DYlHN4sJg==$0.BIN$ixWMQi5sQuS6TWePjKts3g==$0 ACTIVE_DATE CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"ACTIVE_DATE" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CBIN$jSDpAUgDR0yEtuw4gblNiQ==$0 Check BIN$ukEn3nqRT+u/paBe8fJpxQ==$0.BIN$jSDpAUgDR0yEtuw4gblNiQ==$0 LOGICAL_WORKPLACE_ID CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"LOGICAL_WORKPLACE_ID" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CBIN$lYdZT3sESH+sFgqJRWiwMg==$0 Check BIN$2AYTRnCPRfG80DYlHN4sJg==$0.BIN$lYdZT3sESH+sFgqJRWiwMg==$0 ID CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"ID" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CBIN$p/qwfEmtQh2uY1VCYeutOw==$0 Check BIN$z+/2/T9WTRWmOfXZhvGBPQ==$0.BIN$p/qwfEmtQh2uY1VCYeutOw==$0 LOGICAL_WORKPLACE_ID CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"LOGICAL_WORKPLACE_ID" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CBIN$pqYsOQBsTlimA1sF/4BrgA==$0 Check BIN$2AYTRnCPRfG80DYlHN4sJg==$0.BIN$pqYsOQBsTlimA1sF/4BrgA==$0 WORKER_ID CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"WORKER_ID" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CBIN$t8GSYvQHRI6Fjqp5uEhUBQ==$0 Check BIN$2AYTRnCPRfG80DYlHN4sJg==$0.BIN$t8GSYvQHRI6Fjqp5uEhUBQ==$0 LOGICAL_WORKPLACE_ID CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"LOGICAL_WORKPLACE_ID" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CBIN$ucRob1YtSGaFOl8RzJnysA==$0 Check BIN$ukEn3nqRT+u/paBe8fJpxQ==$0.BIN$ucRob1YtSGaFOl8RzJnysA==$0 ID CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"ID" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CBIN$x7NqoC+WQyKj/Ckt/sDBlQ==$0 Check BIN$BIALfQCESHa/SipQl+oqGg==$0.BIN$x7NqoC+WQyKj/Ckt/sDBlQ==$0 ACTIVE_DATE CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"ACTIVE_DATE" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSYS_C004378 Check DDL_AUDIT.SYS_C004378 OBJECT_NAME CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"OBJECT_NAME" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSYS_C004379 Check DDL_AUDIT.SYS_C004379 OBJECT_TYPE CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"OBJECT_TYPE" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSYS_C004380 Check DDL_AUDIT.SYS_C004380 WHEN_CREATED CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"WHEN_CREATED" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSYS_C004381 Check DDL_AUDIT.SYS_C004381 WHO_CREATED CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"WHO_CREATED" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSYS_C004441 Check SITE_TAB.SYS_C004441 SITE_NO CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"SITE_NO" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSYS_C004442 Check SITE_TAB.SYS_C004442 SITE_DESCR CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"SITE_DESCR" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSYS_C004617 Check HAZARD_LEVEL_T.SYS_C004617 ID CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"ID" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSYS_C004618 Check HAZARD_LEVEL_T.SYS_C004618 MAGNITUDE CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"MAGNITUDE" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSYS_C004619 Check HAZARD_LEVEL_T.SYS_C004619 CODE CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"CODE" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSYS_C004620 Check HAZARD_LEVEL_T.SYS_C004620 DESCRIPTION CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"DESCRIPTION" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSYS_C004621 Check HAZARD_LEVEL_T.SYS_C004621 ACTIVE_DATE CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"ACTIVE_DATE" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSYS_C006545 Check WORKING_CUSTOMERS.SYS_C006545 LASTNAME CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"LASTNAME" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSYS_C006546 Check WORKING_CUSTOMERS.SYS_C006546 FIRSTNAME CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
"FIRSTNAME" IS NOT NULL
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
PAVAIL_TRACE_FILES_PK Primary Key AVAIL_TRACE_FILES.AVAIL_TRACE_FILES_PK 1 USERNAME CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
PAVAIL_TRACE_FILES_PK Primary Key AVAIL_TRACE_FILES.AVAIL_TRACE_FILES_PK 2 FILENAME CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
PBIN$m0vd5DhgSUeFjOSJ5z1H0Q==$0 Primary Key BIN$2AYTRnCPRfG80DYlHN4sJg==$0.BIN$m0vd5DhgSUeFjOSJ5z1H0Q==$0 1 ID CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
PEMPLOYEE_EVALUATION_PK Primary Key EMPLOYEE_EVALUATION.EMPLOYEE_EVALUATION_PK 1 ID CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
PEMPLOYEE_EVALUATION_PK Primary Key EMPLOYEE_EVALUATION.EMPLOYEE_EVALUATION_PK 2 TITLE CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
PSYS_C004533 Primary Key HOME_ADDRESS_LIST.SYS_C004533 1 LIST_ID CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
PSYS_C006768 Primary Key P.SYS_C006768 1 X CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
PSYS_C007431 Primary Key MYCLOB.SYS_C007431 1 ID CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
PSYS_C007979 Primary Key I1.SYS_C007979 1 N CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
PSYS_C007980 Primary Key I2.SYS_C007980 1 N CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
PSYS_C007981 Primary Key MAP.SYS_C007981 1 N CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
RSYS_C007982 Foreign Key MAP.SYS_C007982 1 I1 CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
SYS_C007979
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
RSYS_C007983 Foreign Key MAP.SYS_C007983 1 I2 CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
SYS_C007980
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
UBIN$w7oUQV1JTnC+qkix9Qi1eQ==$0 Other BIN$z+/2/T9WTRWmOfXZhvGBPQ==$0.BIN$w7oUQV1JTnC+qkix9Qi1eQ==$0 1 WORKER_ID CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
UBIN$w7oUQV1JTnC+qkix9Qi1eQ==$0 Other BIN$z+/2/T9WTRWmOfXZhvGBPQ==$0.BIN$w7oUQV1JTnC+qkix9Qi1eQ==$0 2 ACTIVE_DATE CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
USYS_C004534 Other HOME_ADDRESS_LIST.SYS_C004534 1 SYS_NC0000200003$ CSORT CTYPE
-----------
CNAME CPOS
----------
COLNAME
CRULE
CREFER
46 rows selected.
SQL></source>
Getting Information on the Constraints on a Column
You can get information on the constraints on a column by querying user_cons_columns.
<source lang="sql">
SQL> desc user_cons_columns;
Name Null? Type ------------------- OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME VARCHAR2(4000) POSITION NUMBER
SQL> SQL> SQL> -- create demo table SQL> create table myTable(
2 id NUMBER(2), 3 value NUMBER(6,2) 4 ) 5 /
Table created. SQL> SQL> -- prepare data SQL> insert into myTable(ID, value)values (1,9)
2 /
1 row created. SQL> insert into myTable(ID, value)values (2,2.11)
2 /
1 row created. SQL> insert into myTable(ID, value)values (3,3.44)
2 /
1 row created. SQL> insert into myTable(ID, value)values (4,-4.21)
2 /
1 row created. SQL> insert into myTable(ID, value)values (5,10)
2 /
1 row created. SQL> insert into myTable(ID, value)values (6,3)
2 /
1 row created. SQL> insert into myTable(ID, value)values (7,-5.88)
2 /
1 row created. SQL> insert into myTable(ID, value)values (8,123.45)
2 /
1 row created. SQL> insert into myTable(ID, value)values (9,98.23)
2 /
1 row created. SQL> SQL> select * from myTable
2 / ID VALUE
----------
1 9 2 2.11 3 3.44 4 -4.21 5 10 6 3 7 -5.88 8 123.45 9 98.23
9 rows selected. SQL> SQL> SQL> SQL> ALTER TABLE myTable
2 ADD CONSTRAINT uq UNIQUE (id) 3 DEFERRABLE INITIALLY DEFERRED;
Table altered. SQL> SQL> COLUMN column_name FORMAT a15 SQL> SELECT constraint_name, column_name
2 FROM user_cons_columns 3 WHERE table_name = "MYTABLE";
CONSTRAINT_NAME COLUMN_NAME
---------------
UQ ID SQL> SQL> clear columns; columns cleared SQL> SQL> -- clean the table SQL> drop table myTable
2 /
Table dropped. SQL> SQL></source>