-
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
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|