Re: setting foreign key to null when deleting primary key

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Christiaan (none_at_nospam.com)
Date: 05/14/04 

Date: Fri, 14 May 2004 18:10:44 +0200

thanx for the reply,
however I was looking for something Celko suggested:
CREATE TABLE Foo
( ...
  x INTEGER
    REFERENCES Bar(x)
    ON DELETE SET NULL,
 ..);
(so not deleting the record with the foreign key, but setting the value of
the foreign key field to null). But if I am not mistaken, Sqlserver doesn't
support this?

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> schreef in bericht
news:kvWdnT52s6j2SzndRVn-jg@giganews.com...
> If child rows exist with the foreign key value of the parent then a delete
> of the parent row will fail unless you have the ON DELETE CASCADE option
> set.
>
> To verify their existence before deleting you would have to query the
child
> table. You could use an INSTEAD OF DELETE trigger to catch the DELETE if
you
> wish.
>
> --
> David Portas
> SQL Server MVP
> --
>
>

Relevant Pages

  • Re: setting foreign key to null when deleting primary key
    ... If child rows exist with the foreign key value of the parent then a delete ... of the parent row will fail unless you have the ON DELETE CASCADE option ...
    (microsoft.public.sqlserver.programming)
  • DataTable. Foreign key does not works.
    ... DataTable has foreign key to itself. ... When I'm deleting one row it's child rows stay in Table. ... DataRow TestRow = TestTable.NewRow; ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Indexes and Foreign Keys
    ... Just on the issue of deleting a PK value. ... I agree one would hopefully only delete a PK value if all the child rows ... Yes lookup parent tables are mostly small, ... fewer than 10) then it is usually just as fast for the cascade delete to do ...
    (comp.databases.oracle.server)
  • Re: Database and Dataset setup.
    ... The first solution is the better choice, but it won't work like that. ... deleting the rows out of the parent table sets the child rows to the deleted ... updating the child table first would try to add the child rows before ...
    (microsoft.public.dotnet.framework.adonet)