Dumb question about cascading deletes in SQL Server 2000

Dumb question about cascading deletes in SQL Server 2000

Post by Dave » Thu, 07 Feb 2002 06:58:49



Do cascading deletes propagate more than one table away from the primary key
table or no?

In other words, if I delete a row in TableA and a row in TableB also gets
deleted because of cascading delete will rows in TableC that have a foreign
key to TableB also get deleted (and so on and so on)?  From my experiments
it doesn't appear to be.  If that's the case, how do you do that?  Have to
use triggers?  If it SHOULD be cascading through all tables, what am I doing
wrong?

 
 
 

Dumb question about cascading deletes in SQL Server 2000

Post by Ivan Arjentinsk » Thu, 07 Feb 2002 07:21:11


It should do this.

Why did you conclude that this is not the case?

--
Ivan Arjentinski
----------------------------------------------------------------------
Please reply to newsgroups. Inclusion of table schemas (CREATE TABLE
scripts), sample data (INSERT INTO....) and desired result set when asking
for assistance is highly appreciated
----------------------------------------------------------------------


Quote:> Do cascading deletes propagate more than one table away from the primary
key
> table or no?

> In other words, if I delete a row in TableA and a row in TableB also gets
> deleted because of cascading delete will rows in TableC that have a
foreign
> key to TableB also get deleted (and so on and so on)?  From my experiments
> it doesn't appear to be.  If that's the case, how do you do that?  Have to
> use triggers?  If it SHOULD be cascading through all tables, what am I
doing
> wrong?


 
 
 

Dumb question about cascading deletes in SQL Server 2000

Post by brido » Thu, 07 Feb 2002 07:19:23


hi!!
if cascade delete is set from one table to another,
if a PK in tableA is deleted
    all FK in tableB gets deleted

if FK in tableB from tableC is present then PK in tableC is not deleted

cascade delete only works from PK to FK and not from FK to PK

i guess thats' what your trying to do!!

:O) asta la vista!!


Quote:> Do cascading deletes propagate more than one table away from the primary
key
> table or no?

> In other words, if I delete a row in TableA and a row in TableB also gets
> deleted because of cascading delete will rows in TableC that have a
foreign
> key to TableB also get deleted (and so on and so on)?  From my experiments
> it doesn't appear to be.  If that's the case, how do you do that?  Have to
> use triggers?  If it SHOULD be cascading through all tables, what am I
doing
> wrong?

 
 
 

Dumb question about cascading deletes in SQL Server 2000

Post by George Li [M » Fri, 08 Feb 2002 22:33:42


The cascading deletes can propagates more than one tables, as long as you
add the "ON DELETE CASCADE" for all the related foreign keys. For example:

create table t1(c1 int not null primary key)
go
create table t2(c1 int foreign key references t1(c1) on delete cascade, c2
int not null primary key)
go
create table t3(c2 int foreign key references t2(c2) on delete cascade, c3
int)
go
insert into t1 values(1)
insert into t2 values(1,1)
insert into t3 values(1,1)

-- after you perform the delete statements, all the data will be deleted.
delete from t1

This posting is provided "AS IS" with no warranties, and confers no rights.

Best Regards,

George Li

Microsoft Support