Output contraints name, column, rule : user_cons_columns « System Tables Views « Oracle PL / SQL






Output contraints name, column, rule

    


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
 10   order by csort, b.position
 11  /

Type        Constraint                           # Column               Constraint Rule                  Refers To
----------- ----------------------------------- -- -------------------- ---------------------------------------- --------------------
Check       WORKING_CUSTOMERS.SYS_C005236          LASTNAME             "LASTNAME" IS NOT NULL
            WORKING_CUSTOMERS.SYS_C005237          FIRSTNAME            "FIRSTNAME" IS NOT NULL
            TMP.SYS_C007377                        STUDENTNAME          "STUDENTNAME" IS NOT NULL
            WORKING_EMPLOYEES.SYS_C008777          LASTNAME             "LASTNAME" IS NOT NULL
            WORKING_EMPLOYEES.SYS_C008778          FIRSTNAME            "FIRSTNAME" IS NOT NULL
            WORKING_EMPS.SYS_C009664               LASTNAME             "LASTNAME" IS NOT NULL
            WORKING_EMPS.SYS_C009665               FIRSTNAME            "FIRSTNAME" IS NOT NULL


Primary Key CUST_WITH_VARRAY_TEMP_TABLE.SYS_C00  1 ID
            4799

            P1.SYS_C005096                       1 X
            IT.SYS_C005376                       1 C1
            CUST_NO_KEY_TABLE.SYS_C005404        1 CUST_NO
            DEMO.SYS_C005536                     1 X
            P.SYS_C005543                        1 PK
            INVENTORY_TBL.SYS_C006948            1 ITEM_ID
            EMPLOYEETEMPTABLE.SYS_C008156        1 ID
            EMPTEMPTABLE.SYS_C009072             1 ID
            IX.SYS_IOT_TOP_16135                 1 X



17 rows selected.

SQL> spool off
SQL>

   
    
    
    
  








Related examples in the same category

1.Combine user_constraints and user_cons_columns table