Просмотр исходного текста страницы Oracle PL/SQL Tutorial/System Tables Data Dictionary/all cons columns

== Get information on all the column constraints you have access to using all_cons_columns.==



   
  <!-- start source code -->
   
    <source lang="sql">
SQL>
SQL> desc all_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> --select * from all_cons_columns;
SQL></source>
    
   

== Join user_constraints and user_cons_columns to get the column_name, constraint_name, constraint_type, and status==



   
  <!-- start source code -->
   
    <source lang="sql">
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>
SQL> SELECT ucc.column_name, ucc.constraint_name, uc.constraint_type, uc.status
  2  FROM user_constraints uc, user_cons_columns ucc
  3  WHERE uc.table_name = ucc.table_name
  4  AND uc.constraint_name = ucc.constraint_name
  5  AND ucc.table_name = "MYTABLE";
COLUMN_NAME          CONSTRAINT_NAME                C STATUS
------------------------------ - --------
ID                   UQ                             U ENABLED

SQL>
SQL>
SQL> -- clean the table
SQL> drop table myTable
  2  /
Table dropped.
SQL>
SQL>
SQL></source>