Re: ON DELETE SET NULL does not work???

From: Steve Ashmore <sashmore_at_neonramp.com>
Date: Wed, 2 Oct 2002 05:43:11 -0500
Message-ID: <upljdi5ufnl8ac@corp.supernews.com>

Yes it does.
There is a bug where 'set null' does not show up in dba_constraints.

You need to check sys.con$.

Try This:

REM List SET NULL RI COnstraints. There is a bug in the REM DBA_CONSTRAINTS table

select ou.name as OWNER, oc.name as CONSTRAINT_NAME,

       decode(c.type#, 1, 'C', 2, 'P', 3, 'U',
              4, 'R', 5, 'V', 6, 'O', 7,'C', '?') as CONSTRAINT_TYPE,
       o.name as TABLE_NAME,  ru.name as R_OWNER,
           rc.name As R_CONSTRAINT_NAME,
             decode(c.refact, 1, 'CASCADE', 2, 'SET NULL', 'NO ACTION') as
           DELETE_RULE
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 and c.refact = 2 /

Stephen C. Ashmore
Brainbench MVP for Oracle Administration http://www.brainbench.com

Author of: 'So You Want to be an Oracle DBA?'

"Sergey Balter" <balter_at_kompas.donetsk.ua> wrote in message news:anebpc$jgq$1_at_dipt.donbass.net...
> Hi,
>
> A am trying to alter table and add constraint
>
> ALTER TABLE Lot
> ADD FOREIGN KEY (ProductId)
> REFERENCES Product (Id)
> ON DELETE SET NULL;
>
> There are no error messages.
>
> But when I check what happens with
>
> SELECT * FROM USER_COSNTRAINTS
> WHERE Table_Name = 'LOT'
>
> I've got 'NO_ACTION' instead of 'SET NULL' in DELETE_RULE field.
>
> CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME DELETE_RULE
> SYS_C0075654 R
> LOT NO ACTION
>
>
> I use Oracle 8.1.7
>
> Regards,
> Sergey Balter
>
>
Received on Wed Oct 02 2002 - 05:43:11 CDT