dba_constraints
Name Null? Type
----------------------- -------- ------------------
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(30)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)
Displays the constraints present on a table. For example, the following table has a single primary key constraint:
SQL>
create table andy.test_table(
id number
, text char(10) primary key
)
/
Table created.
DBA_CONSTRAINTS will return the following:
select constraint_type
, constraint_name cons_name
, status
, last_change
from dba_constraints
where owner like 'ANDY'
and table_name like 'TEST_TABLE'
/
CONSTRAINT_TYPE CONS_NAME STATUS LAST_CHAN
---------------- ------------------------------ -------- ---------
P SYS_C0027234 ENABLED 17-JAN-08
The values in the 'constraint_type' column have the following meanings:
| C | - Check constraint |
| O | - R/O View |
| P | - Primary key |
| R | - Foreign key |
| U | - Unique key |
| V | - Check view |
Taking this into account the above query could be re-written as:
select decode(constraint_type,
'C', 'Check',
'O', 'R/O View',
'P', 'Primary',
'R', 'Foreign',
'U', 'Unique',
'V', 'Check view') type
, constraint_name cons_name
, status
, last_change
from dba_constraints
where owner like 'ANDY'
and table_name like 'TEST_TABLE'
/
For more useful DBA queries click here.
|
