Delete_Rule-Value (FK) is not correct in dba_constraints after changing it
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

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

  1. #1
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448

    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

  2. #2
    Join Date
    Dec 2001
    Posts
    141
    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 !

  3. #3
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Thank you for this detailed help!
    God bless

    Orca

  4. #4
    Join Date
    Dec 2001
    Posts
    141
    You are welcome !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width