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

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'

Code:

-- 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

Code:

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

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 !

Thank you for this detailed help!
God bless

Orca
:cool:

You are welcome !