Ms SQL "On Delete Cascade" Multiple Foreign Keys Pointing to the Same Table

MS SQL ON DELETE CASCADE multiple foreign keys pointing to the same table?

You'll have to implement this as an INSTEAD OF delete trigger on insights, to get it to work. Something like:

create trigger T_Insights_D
on Insights
instead of delete
as
    set nocount on
    delete from broader_insights_insights
    where insight_id in (select ID from deleted) or
    broader_insight_id in (select ID from deleted)

    delete from Insights where ID in (select ID from deleted)

---

Frequently with cascading deletes and lots of foreign keys, you need to spend time to work out a "cascade" order so that the delete that occurs at the top of a "tree" is successfully cascaded to referencing tables. But that isn't possible in this case.

Two foreign keys on same table: how to implement on delete cascade?

use this trigger :
this trigger acts instead of delete. therefore you must delete record(s) after set references to null.

 CREATE TRIGGER Trigger_TBL_ANOTHER_TABLE
    ON [dbo].[TBL_ANOTHER_TABLE]
    INSTEAD OF DELETE
    AS 
    BEGIN

        SET NOCOUNT ON;

        UPDATE  t
        SET     t.ID1 = NULL
        from [dbo].[TBL_TOGETHER] t inner join
        deleted d on d.ID = t.ID1

        UPDATE  t
        SET     t.ID2 = NULL
        from [dbo].[TBL_TOGETHER] t inner join
        deleted d on d.ID = t.ID2

        DELETE  t
        from [dbo].[TBL_ANOTHER_TABLE] t inner join
        deleted d on d.ID = t.ID

    END
    GO

Having multiple foreign keys to same table with different on delete causes error

This is well known SQL Server behaviour, two or more on delete paths from a child to a parent are not allowed. This will run ok for example

CREATE TABLE [dbo].[InstallerDetails]
(
    [UserId] int not null PRIMARY KEY foreign key references Users(Id) on delete cascade
    ,[AssignedSalesman] int null foreign key references Users(Id) 
)

You can enforce missing on delete set null with a trigger.

on cascade delete on a table with two FK to the same table

You can't have multiple or circular cascade paths: it becomes ambiguous what you want to do (say one CASCADE NULL and the other CASCADE DELETE)

I'd use a stored procedure to delete from Friends first in a transaction then from Users (in a TRY/CATCH of course to deal with errors)

BEGIN TRAN
   DELETE Friends WHERE User1ID = @UserID;
   DELETE Friends WHERE User2ID = @UserID;
   DELETE Users WHERE UserID = @UserID;
COMMIT TRAN

MSSQL: how to have multiple cascades with referencing the same table

By creating a trigger that first deletes the sub-level table records and then itself after using the instead of delete you can trigger the sub triggers like a cascading pattern executing in the right order.

This solves the issue of cascade by adding a little more logic. Also you may want to use the primary key instead of the ID here for correct record selections on bigger keys.

drop table table4

drop table table2

drop table table3

drop table table1

drop table table0



create table table0 (

   id integer not null primary key

)



create table table1 (

   id integer not null primary key

)



create table table2 (

   id integer not null primary key,

   table0_id integer not null,

   table1_id integer not null

)



create table table3 (

   id integer not null primary key,

   table1_id integer not null

)



create table table4 (

   id integer not null primary key,

   table2_id integer not null,

   table3_id integer not null

)



alter table table2 add constraint fk_table2_table0 foreign key (table0_id)

references table0 (id) on delete cascade on update no action



alter table table2 add constraint fk_table2_table1 foreign key (table1_id)

references table1 (id) on delete cascade on update no action



alter table table3 add constraint fk_table3_table1 foreign key (table1_id)

references table1(id) on delete no action on update no action



alter table table4 add constraint fk_table4_table2 foreign key (table2_id)

references table2(id) on delete cascade on update no action



alter table table4 add constraint fk_table4_table3 foreign key (table3_id)

references table3(id) on delete no action on update no action



GO

CREATE TRIGGER WhenRowFromTable3IsDeleted ON table3

   INSTEAD OF DELETE      

AS

BEGIN

   DELETE FROM table4 WHERE table3_id = (SELECT id FROM DELETED)

    DELETE FROM table3 WHERE id = (SELECT id FROM DELETED)

END

GO

CREATE TRIGGER WhenRowFromTable1IsDeleted ON table1

   INSTEAD OF DELETE      

AS

BEGIN

   DELETE FROM table3 WHERE table1_id = (SELECT id FROM DELETED)

    DELETE FROM table1 WHERE id = (SELECT id FROM DELETED)

END

GO



INSERT INTO table0 (id) VALUES (1)

INSERT INTO table1 (id) VALUES (1)

INSERT INTO table2 (id, table0_id, table1_id) VALUES (1, 1, 1)

INSERT INTO table3 (id, table1_id) VALUES (1, 1)

INSERT INTO table4 (id, table2_id, table3_id) VALUES (1,1,1)



DELETE FROM table3 WHERE id = 1



SELECT * FROM table1, table0

SQLITE: Multiple foreign key referenced to multiple table cascade delete

You may use cascading deletion here. For example, for TableC, you might use the following definition:

CREATE TABLE TableC (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    column1 TEXT,
    tb_bid INTEGER NOT NULL,
    CONSTRAINT fk_b FOREIGN KEY (tb_bid) REFERENCES TableB (id) ON DELETE CASCADE
)

The purpose of the constraint in the above table definition is that it links the tb_bid column as a foreign key pointing to the primary key id column in TableB. This means that if a parent record in TableB gets deleted, SQLite will cascade that deletion down to all child records in TableC.

You might define your TableB as follows:

CREATE TABLE TableB (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    issues TEXT,
    tb_aid INTEGER NOT NULL,
    tb_cid INTEGER NOT NULL,
    tb_did INTEGER NOT NULL,
    tb_eid INTEGER NOT NULL,
    CONSTRAINT fk_a FOREIGN KEY (tb_aid) REFERENCES TableA (id) ON DELETE CASCADE,
    CONSTRAINT fk_c FOREIGN KEY (tb_cid) REFERENCES TableC (id) ON DELETE CASCADE,
    CONSTRAINT fk_d FOREIGN KEY (tb_did) REFERENCES TableD (id) ON DELETE CASCADE,
    CONSTRAINT fk_e FOREIGN KEY (tb_eid) REFERENCES TableE (id) ON DELETE CASCADE
)

Remove row from table (cascade) that have 2 foreign keys

The problem is the cascade order. You have follow this sequence:

1. Delete rooms
2. Delete images
3. Delete houses

You need create a trigger to solve that. Now the problem is I dont know how create that using Code First.

create trigger house_cascade
on Houses
instead of delete
as
   set nocount on
   delete from rooms
   where room.id in (select i.ID 
                     from images i
                     inner join deleted d
                        on i.house_id = d.id)

   delete from images 
   where house_id in ( select id from deleted )

   delete from houses 
   where ID in ( select ID from deleted )

More info here

Especially this comment

But SQL Server doesn't support this. It's supper annoying, no other serious DB engine has this problem, people complained about it in 2005, Microsfot agreed that it was a "desirable feature" in 2008, but still here in 2014 they don't have it. – Shavais Aug 5 '14 at 21:28

---

Related Topics