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;