Delete_Rule-Value (FK) is not correct in dba_constraints after changing it

[Orca777]

Delete_Rule-Value is not correct in dba_constraints after changing it

Oracle EE 8.1.7.3.50


I changed the DELETE_RULE to 'Set null'

-- Create/Recreate primary, unique and foreign key constraints
alter table T_CHILD
  drop constraint T_CHILD_T_PARENT_FK;
alter table T_CHILD
  add constraint T_CHILD_T_PARENT_FK foreign key (ID)
  references T_PARENT (ID) on delete set null;

DBA_CONSTRAINTS

CONSTRAINT_NAME                CONSTRAINT_TYPE TABLE_NAME                     DELETE_RULE STATUS   DEFERRABLE     DEFERRED  VALIDATED     GENERATED      BAD RELY LAST_CHANGE
------------------------------ --------------- ------------------------------ ----------- -------- -------------- --------- ------------- -------------- --- ---- -----------
T_PARENT_PK                    P               T_PARENT                                   ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED     USER NAME               07.01.2003
T_CHILD_T_PARENT_FK            R               T_CHILD                        NO ACTION   ENABLED  NOT DEFERRABLE IMMEDIATE VALIDATED     USER NAME               07.01.2003

But its val is still 'NO ACTION'
But the DDL works : when i delete parent-rows the child-id is set to null
Where does oracle know what to do?

Orca

[htanga]

Hi !
There is bug filed against this issue and the bug number is
1775864:*_CONSTRAINTS VIEWS SHOW WRONG DATA

This is not a published bug and it is under resolution stage.

You could find this information in the dictionary table cdef$ with the refact column.
The possible values are :
1 : ON DELETE CASCADE
2 : ON DELETE SET NULL
3 : NO ACTION

So you could create your own dba_constraints view like this :

create or replace view myown_view_constraints
as
select ou.name, oc.name,
decode(c.type#, 1, 'C', 2, 'P', 3, 'U',
4, 'R', 5, 'V', 6, 'O', 7,'C', '?'),
o.name, c.condition, ru.name, rc.name,
decode(c.type#, 4,
decode(c.refact, 1, 'CASCADE', 2, 'SET NULL', 'NO ACTION'), NULL),
decode(c.type#, 5, 'ENABLED',
decode(c.enabled, NULL, 'DISABLED', 'ENABLED')),
decode(bitand(c.defer, 1), 1, 'DEFERRABLE', 'NOT DEFERRABLE'),
decode(bitand(c.defer, 2), 2, 'DEFERRED', 'IMMEDIATE'),
decode(bitand(c.defer, 4), 4, 'VALIDATED', 'NOT VALIDATED'),
decode(bitand(c.defer, 8), 8, 'GENERATED NAME', 'USER NAME'),
decode(bitand(c.defer,16),16, 'BAD', null),
decode(bitand(c.defer,32),32, 'RELY', null),
c.mtime
from sys.con$ oc, sys.con$ rc, sys.user$ ou, sys.user$ ru,
sys.obj$ o, sys.cdef$ c
where oc.owner# = ou.user#
and oc.con# = c.con#
and c.obj# = o.obj#
and c.type# != 8 /* don't include hash expressions */
and c.rcon# = rc.con#(+)
and rc.owner# = ru.user#(+)
/

Because the views *_constraints don't treat the "set null" ...
Hope this help !

[Orca777]

Thank you for this detailed help!
God bless

Orca

[htanga]

You are welcome !