Oracle PL/SQL Tutorial/System Tables Data Dictionary/user cons columns

Материал из SQL эксперт
Перейти к: навигация, поиск

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>