Oracle PL/SQL/System Tables Views/USER CONSTRAINTS

Get a list of constraints defined on the employee table from User_Constraints

  
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
  3  /
1 row created.
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
  3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /
ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
02   Alison     Mathews    21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03   James      Smith      12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester
04   Celia      Rice       24-OCT-82 21-APR-99    2344.78 Vancouver  Manager
05   Robert     Black      15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06   Linda      Green      30-JUL-87 04-JAN-96    4322.78 New York   Tester
07   David      Larry      31-DEC-90 12-FEB-98    7897.78 New York   Manager
08   James      Cat        17-SEP-96 15-APR-02    1232.78 Vancouver  Tester
8 rows selected.
SQL>
SQL>
SQL> --Get a list of constraints defined on the employee table
SQL>
SQL> SELECT constraint_name, constraint_type,
  2  search_condition
  3  FROM USER_CONSTRAINTS
  4  WHERE table_name ="EMPLOYEE";
CONSTRAINT_NAME                C SEARCH_CONDITION
------------------------------ - --------------------------------------------------------------------------------
SYS_C004876                    C "ID" IS NOT NULL
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL>
SQL>
SQL>

Join user_constraints and user_cons_columns table

   
SQL>
SQL> SELECT dc.table_name, dcc.column_name, dcc.position,
  2         dcc.constraint_name, dc.constraint_type, dc.search_condition
  3  FROM   user_constraints dc, user_cons_columns dcc
  4  WHERE  dcc.table_name      = dc.table_name
  5  AND    dcc.owner           = dc.owner
  6  AND    dcc.constraint_name = dc.constraint_name
  7  and    rownum < 50
  8  ORDER BY dcc.table_name, dcc.owner, dcc.position, dcc.column_name;
no rows selected

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>

Query user_constraints for constriant name, type, table name

  
SQL>
SQL>
SQL> select constraint_name, constraint_type,
  2         table_name, r_constraint_name
  3    from user_constraints
  4   where constraint_name = "DEPARTMENT_FK_MGR_NO";
no rows selected