SQL Server, Cascade Delete and Parent/Child Table

How to CASCADE a delete from a child table to the parent table?

Foreign keys only work in the other direction: cascade deletes from parent to child, so when the parent (referenced) record is deleted, any child (referencing) records are also deleted.

If it's a 1:1 relationship you can create a bi-directional foreign key relationship, where one side is DEFERRABLE INITIALLY DEFERRED, and both sides are cascade.

Otherwise, you will want an ON DELETE ... FOR EACH ROW trigger on the child table that removes the parent row if there are no remaining children. It's potentially prone to race conditions with concurrent INSERTs; you'll need to SELECT ... FOR UPDATE the parent record, then check for other child records. Foreign key checks on insert take a FOR SHARE lock on the referenced (parent) record, so that should prevent any race condition.

Delete from child table with two parents without cascade delete in sql server

Try something like this:

DECLARE @RowsToDelete TABLE
(
    RowsToDeleteID int IDENTITY(1,1) PRIMARY KEY,
    Table1ID int,
    Table2ID int,
    Table3ID int
);

INSERT @RowsToDelete (Table1ID, Table2ID, Table3ID)
SELECT DISTINCT t1.t1ID, t2.t2ID, t3.t3ID 
FROM table3 AS t3
INNER JOIN table1 AS t1 
ON t3.t1ID = t1.t1ID
INNER JOIN table2 AS t2 
ON t2.t1ID = t1.t1ID
WHERE t2.t2ID IS NULL 
AND t1.LastDate < @UpDate;

DELETE table2
WHERE EXISTS (SELECT 1 FROM @RowsToDelete AS rtd WHERE rtd.Table2ID = t2ID);

DELETE table1
WHERE EXISTS (SELECT 1 FROM @RowsToDelete AS rtd WHERE rtd.Table1ID = t1ID);

DELETE table3
WHERE EXISTS (SELECT 1 FROM @RowsToDelete AS rtd WHERE rtd.Table3ID = t3ID);

Haven't got the tables, etc. to try it but looks like it should be ok.

I'm also presuming here that those rows aren't possibly changing while you're doing this, otherwise you have potential race conditions.

Will ON DELETE CASCADE delete a parent even if another child has it as parent?

There is a misconception here. The foreign key binds a child to a parent - not the other way around.

You seem to assume that on delete cascade deletes the parent when a chidren is removed. This is not the case: when a parent is deleted, the related children are deleted as well.

Here is an example based on your setup:

create table parents (
    id int not null,
    primary key (id)
);

create table children (
    id int, 
    parent_id int,
    foreign key (parent_id) references parents(id) on delete cascade
);

-- parent 1 has two children, parent 2 has one child
insert into parents values (1), (2);
insert into children values (1, 1), (2, 1), (3, 2);

-- delete parent 1
delete from parents where id = 1;

-- related children where deleted
select * from children;

id | parent_id
-: | --------:
 3 |         2

On Delete Cascade if deleting a row from the child table

It would be deleted and nothing else would happen. Cascading deletes only go from the referenced table to the referencing table.
So a delete on table X will cascade a delete down to table y, while a delete on table y has no impact on table x.

---

Related Topics