CODEWITHSUNDEEP
sql-serverparent-childcascade
Ben
Ben

Reputation: 1

on delete cascade is deleting entire child table

I have two tables: Parent, which has a primary key (parentID) and some other fields of type varchar(50) and Child which is as follows:

childID, primary key
parentID, foreign key references Parent (parentID)
otherID, foreign key  to another table

Now, I modify the relationship for the parentID key and set delete to cascade instead of No Action (I'm using the 2008 server studio, right-clicking on column, choose Modify, then right click on the table design and choose Relationships. I then choose the relationship for the Parent/Child tables and open the Insert/Update Specification section under Table Designer)

If I delete a row from Child, again, no problem. If I delete a row from Parent, ALL the rows of the child table are deleted, even through most of them point to other parentIDs.

I inherited this DB and all it's tables. Is there some properties settings I'm missing? From what I've read, the cascade delete should ONLY delete the child rows that point to the single parentID I deleted from Parent.

Thanks.

Ben

Upvotes: 0

Views: 1216

Answers (2)

Syed Danish Badr
Syed Danish Badr

Reputation: 11

Try setting SetNull instead of cascade, which results setting your child table column (where Parent Table's key exit) to NULL.

Upvotes: 1

Phil Sandler
Phil Sandler

Reputation: 28016

Right, the delete should only delete rows related to the deleted "parent".

Take a look at the cascading delete setup, and also look for triggers that may be incorrectly written.

Upvotes: 2

Related Questions