Re: on delete set null

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 11 Jan 2000 08:14:27 -0500
Message-ID: <6kam7sk9pt73tdfmplbv27bvlgvp00tqhv@4ax.com>

A copy of this was sent to Doug Cowles <dcowles_at_us.ibm.com> (if that email address didn't require changing) On Mon, 10 Jan 2000 16:14:24 -0500, you wrote:

>I am happy that on delete set null works in Oracle 8i. However, I see
>no confirmation of it's implementation in user_constraints. The delete
>rule still specifies NO ACTION ENBLED, even though it is clearly
>implemented with add contraint xyz foreing key (id)
>refereneces othertable(id) on delete set null.
>What am I missing?
>- Dc.
 

looks like a bug -- they didn't update the decode in the *_constraints view to have 'SET NULL' in it. The current view is:

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', '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.rcon# = rc.con#(+)
  and rc.owner# = ru.user#(+)
  and o.owner# = userenv('SCHEMAID')
  and c.type# != 8
/

In the meantime, you could write your own view that fixes the decode to be:

       decode(c.type#, 4,
              decode(c.refact, 1, 'CASCADE', 2, 'SET NULL', 'NO ACTION'), NULL),

I don't recommend replacing the user_constraints view with this new view but rather creating your OWN view that you use in place of it....

I filed bug 1147298 for this.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jan 11 2000 - 07:14:27 CST