Structure - Constraints

Show all constraints on a table

col type format a10
col cons_name format a30
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 '&owner'
and table_name like '&table_name'
order by 1
/
Output:

Enter value for owner: ANDY
old  12: where  owner like '&owner'
new  12: where  owner like 'ANDY'
Enter value for table_name: LOCATION
old  13: and    table_name like '&table_name'
new  13: and    table_name like 'LOCATION'

TYPE       CONS_NAME                      STATUS   LAST_CHAN
---------- ------------------------------ -------- ---------
Primary    LOCATION_PK                    ENABLED  15-OCT-07

1 row selected.

List tables that are using the specified table as a foreign key

set lines 100 pages 999
select a.owner
, a.table_name
, a.constraint_name
from dba_constraints a
, dba_constraints b
where a.constraint_type = 'R'
and  a.r_constraint_name = b.constraint_name
and  a.r_owner  = b.owner
and  b.owner = '&table_owner'
and b.table_name = '&table_name'
/
Output:

Enter value for table_owner: ANDY
old   9: and    b.owner = '&table_owner'
new   9: and    b.owner = 'ANDY'
Enter value for table_name: LOCATION
old  10: and    b.table_name = '&table_name'
new  10: and    b.table_name = 'LOCATION'

OWNER           TABLE_NAME                     CONSTRAINT_NAME
--------------- ------------------------------ ------------------------------
ANDY            BOX                            BOX_LOCATION_FK

1 row selected.

Same as above, but produces 'disable constraint' statements
set lines 100 pages 999
col discon format a100 
select 'alter table '||a.owner||'.'||a.table_name||' disable constraint
'||a.constraint_name||';' discon
from dba_constraints a
, dba_constraints b
where a.constraint_type = 'R'
and  a.r_constraint_name = b.constraint_name
and a.r_owner  = b.owner
and  b.owner = '&table_owner'
and b.table_name = '&table_name'
/




Produce a list of disabled fk constraints
set lines 100 pages 999
col table format a60
col constraint_name format a30
select owner||'.'||table_name "table"
, constraint_name
from dba_constraints
where status = 'DISABLED'
and constraint_type = 'R'
and owner not in ('SYS','SYSTEM')
order by 1,2
/




Produce enable statements all disabled fk constraints
set lines 100 pages 999
select 'alter table '||owner||'.'||table_name||' enable constraint
'||constraint_name||';' "enable"
from dba_constraints
where status = 'DISABLED'
and constraint_type = 'R'
and owner not in ('SYS','SYSTEM')
order by 1
/






List parent tables that may need fixing/re-importing
select distinct r.owner || '.' || r.table_name "exp"
from dba_constraints c
, dba_constraints r
where c.status = 'DISABLED'
and c.constraint_type = 'R'
and c.r_owner = r.owner
and c.r_constraint_name = r.constraint_name
and c.owner not in ('SYS','SYSTEM')
order by 1
/




List missing foriegn key values

Note. Useful for resolving ORA-02298
select  'select '||cc.column_name-
        ||' from '||c.owner||'.'||c.table_name-
        ||' a where not exists (select ''x'' from '-
        ||r.owner||'.'||r.table_name-
        ||' where '||rc.column_name||' = a.'||cc.column_name||')'
from    dba_constraints c,
        dba_constraints r,
        dba_cons_columns cc,
        dba_cons_columns rc
where   c.constraint_type = 'R'
and     c.owner not in ('SYS','SYSTEM')
and     c.r_owner = r.owner
and     c.owner = cc.owner
and     r.owner = rc.owner
and     c.constraint_name = cc.constraint_name
and     r.constraint_name = rc.constraint_name
and     c.r_constraint_name = r.constraint_name
and     cc.position = rc.position
and     c.owner = '&table_owner'
and     c.table_name = '&table_name'
and     c.constraint_name = '&constraint_name'
order   by c.owner, c.table_name, c.constraint_name, cc.position
/






Show all table constraints for a user

Note. This still needs some work...
set lines 100 pages 999
break on table_name
select  table_name
, decode(constraint_type, 
         'C', 'Check', 
         'O', 'R/O View', 
         'P', 'Primary', 
         'R', 'Foreign', 
         'U', 'Unique', 
         'V', 'Check view') type
, nvl(index_name, R_CONSTRAINT_NAME) "IDX"
from  dba_constraints
where owner like '&user'
order by table_name
, decode(constraint_type, 
 'P','0','R','1','U','2','C','3','O','4','V','5')
/




Output:

Enter value for user: ANDY
old  11: where  owner like '&user'
new  11: where  owner like 'ANDY'

TABLE_NAME                     TYPE       IDX
------------------------------ ---------- ------------------------------
BOX                            Primary    BOX_PK
                               Foreign    LOCATION_PK
CD                             Primary    CD_PK
                               Foreign    BOX_PK
LOCATION                       Primary    LOCATION_PK
LOTS_OF_ROWS                   Primary    SYS_C0023467
TEST_TABLE                     Primary    SYS_C0027234
TRACK                          Primary    TRACK_PK
                               Foreign    CD_PK

9 rows selected.