dba_cons_columns
Name Null? Type
-------------------- -------- ---------------
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)
POSITION NUMBER
DBA_CONS_COLUMNS describes table columns that have constraints on them. So for example, lets create a simple table with a constraint on one column:
SQL>
create table andy.test_table(
id number primary key
, text char(10)
)
/
Table created.
As you can see, the column 'id' has a primary key constraint defined. If we query dba_cons_columns we should see an entry for it:
select *
from dba_cons_columns
where owner = 'ANDY'
and table_name = 'TEST_TABLE'
/
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
------ -------------------- -------------------- -------------------- ----------
ANDY SYS_C0027234 TEST_TABLE ID 1
The primary key constraint will be enforce by an unique index on the ID column:
select owner
, index_name
, table_name
uniqueness
from dba_indexes
where owner = 'ANDY'
and table_name = 'TEST_TABLE'
/
OWNER INDEX_NAME TABLE_NAME UNIQUENES
------ ------------------------------ -------------------- ---------
ANDY SYS_C0027234 TEST_TABLE UNIQUE
For more useful DBA queries click here.
| 