DBA_CONSTRAINTS

Constraint definitions on all tables

Name Datatype Length Mandatory Comments
OWNER VARCHAR2 (30) Yes Owner of the table
CONSTRAINT_NAME VARCHAR2 (30) Yes Name associated with constraint definition
CONSTRAINT_TYPE VARCHAR2 (1)   Type of constraint definition
TABLE_NAME VARCHAR2 (30) Yes Name associated with table with constraint definition
SEARCH_CONDITION LONG (0)   Text of search condition for table check
R_OWNER VARCHAR2 (30)   Owner of table used in referential constraint
R_CONSTRAINT_NAME VARCHAR2 (30)   Name of unique constraint definition for referenced table
DELETE_RULE VARCHAR2 (9)   The delete rule for a referential constraint
STATUS VARCHAR2 (8)   Enforcement status of constraint - ENABLED or DISABLED
DEFERRABLE VARCHAR2 (14)   Is the constraint deferrable - DEFERRABLE or NOT DEFERRABLE
DEFERRED VARCHAR2 (9)   Is the constraint deferred by default - DEFERRED or IMMEDIATE
VALIDATED VARCHAR2 (13)   Was this constraint system validated? - VALIDATED or NOT VALIDATED
GENERATED VARCHAR2 (14)   Was the constraint name system generated? - GENERATED NAME or USER NAME
BAD VARCHAR2 (3)   Creating this constraint should give ORA-02436. Rewrite it before 2000 AD.
RELY VARCHAR2 (4)   If set, this flag will be used in optimizer
LAST_CHANGE DATE     The date when this column was last enabled or disabled
INDEX_OWNER VARCHAR2 (30)   The owner of the index used by this constraint
INDEX_NAME VARCHAR2 (30)   The index used by this constraint
INVALID VARCHAR2 (7)    
VIEW_RELATED VARCHAR2 (14)    

Usage:

cursor cur_primary_key(cv_table_name in dba_constraints.table_name%type
                     , c_owner in dba_objects.owner%type)
  -- List the primary key
  is select uc.constraint_name
     from dba_constraints uc
     where uc.table_name = cv_table_name
     and uc.owner = c_owner
     and uc.constraint_type = 'P'
     order by uc.constraint_name;
--
cursor cur_unique_keys(cv_table_name in dba_constraints.table_name%type
                     , c_owner in dba_objects.owner%type)
  -- List the unique key constraints
  is select uc.constraint_name
     from dba_constraints uc
     where uc.table_name = cv_table_name
     and uc.constraint_type = 'U'
     and uc.owner = c_owner
     order by uc.constraint_name;