Output user constraints in details : USER_CONSTRAINTS « System Tables Views « Oracle PL / SQL

1. Oracle PL / SQL
2. System Tables Views
3. USER_CONSTRAINTS

Output user constraints in details

  
SQL>
SQL>
SQL>
SQL> col   constraint_name format a15
SQL> col   table_name      format a15
SQL> col   type            format a6
SQL> break on table_name   page
SQL>
SQL> select   table_name
  2  ,        constraint_name
  3  ,        case constraint_type
  4                when 'P' then 'PRIKEY'
  5                when 'U' then 'UNIQUE'
  6                when 'C' then ' CHECK'
  7                when 'R' then 'FORKEY'
  8                         else ' OTHER'
  9           end  as type
 10  ,        status
 11  from     user_constraints
 12  order by table_name
 13  ,        type;

TABLE_NAME      CONSTRAINT_NAME TYPE   STATUS
--------------- --------------- ------ --------
CUST_NO_KEY_TAB SYS_C005404     PRIKEY ENABLED
LE


TABLE_NAME      CONSTRAINT_NAME TYPE   STATUS
--------------- --------------- ------ --------
CUST_WITH_VARRA SYS_C004799     PRIKEY ENABLED
Y_TEMP_TABLE


TABLE_NAME      CONSTRAINT_NAME TYPE   STATUS
--------------- --------------- ------ --------
DEMO            SYS_C005536     PRIKEY ENABLED

TABLE_NAME      CONSTRAINT_NAME TYPE   STATUS
--------------- --------------- ------ --------
EMPLOYEETEMPTAB SYS_C008156     PRIKEY ENABLED
LE


TABLE_NAME      CONSTRAINT_NAME TYPE   STATUS
--------------- --------------- ------ --------
EMPTEMPTABLE    SYS_C009072     PRIKEY ENABLED

TABLE_NAME      CONSTRAINT_NAME TYPE   STATUS
--------------- --------------- ------ --------
INVENTORY_TBL   SYS_C006948     PRIKEY ENABLED

TABLE_NAME      CONSTRAINT_NAME TYPE   STATUS
--------------- --------------- ------ --------
IT              SYS_C005376     PRIKEY ENABLED

TABLE_NAME      CONSTRAINT_NAME TYPE   STATUS
--------------- --------------- ------ --------
IX              SYS_IOT_TOP_161 PRIKEY ENABLED
                35


TABLE_NAME      CONSTRAINT_NAME TYPE   STATUS
--------------- --------------- ------ --------
MYTABLE1        SYS_C009993     PRIKEY ENABLED

TABLE_NAME      CONSTRAINT_NAME TYPE   STATUS
--------------- --------------- ------ --------
P               SYS_C0010267    PRIKEY ENABLED

TABLE_NAME      CONSTRAINT_NAME TYPE   STATUS
--------------- --------------- ------ --------
P1              SYS_C005096     PRIKEY ENABLED

TABLE_NAME      CONSTRAINT_NAME TYPE   STATUS
--------------- --------------- ------ --------
TMP             SYS_C007377      CHECK ENABLED

TABLE_NAME      CONSTRAINT_NAME TYPE   STATUS
--------------- --------------- ------ --------
WORKING_CUSTOME SYS_C005237      CHECK ENABLED
RS

                SYS_C005236      CHECK ENABLED

TABLE_NAME      CONSTRAINT_NAME TYPE   STATUS
--------------- --------------- ------ --------
WORKING_EMPLOYE SYS_C008778      CHECK ENABLED
ES

                SYS_C008777      CHECK ENABLED

TABLE_NAME      CONSTRAINT_NAME TYPE   STATUS
--------------- --------------- ------ --------
WORKING_EMPS    SYS_C009664      CHECK ENABLED
                SYS_C009665      CHECK ENABLED

18 rows selected.

SQL>
SQL> --clear breaks
SQL>

   
    
  

Related examples in the same category