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

Question

I have next table structure :

RoomId and HouseId are nullable, one field is always null. I got next exception while attempt to delete row from Houses table.

The DELETE statement conflicted with the REFERENCE constraint "FK_dbo.Images_dbo.Rooms_RoomId"

Why the cascade delete fails? I have used EF code first.

EDIT

Working trigger based on @Juan Carlos example:

BEGIN TRANSACTION

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

   delete from images 
   where roomId in ( 
                       select rooms.Id
                       from rooms 
                       where rooms.HouseId in (select id from deleted)
                   )

   delete from Rooms 
   where houseId in ( select id from deleted )

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

COMMIT

@JuanCarlosOropeza, I used entity framework code first.But I assume this issue doesn't relate to ORM.But in any case I will update question. — Anton Putau, Sep 11 '15 at 18:34
But if you did Code first, that create a db doesnt it? So what db is? SQL Server, Postgres? What schema was create in db? The idea is you remove the application part and make sure db is working ok. If you can delete direct on the db then the problem is app, if you cant the the db is wrong. — Juan Carlos Oropeza, Sep 11 '15 at 19:28
@JuanCarlosOropeza server - SQL Server, sheme - dbo, error still occur while attempt to manual delete row with the same message. I know that db is wrong (that is why in original message I havent specifyed EF as ORM), otherwise there were no exception.But I can't see it - I still assume that if consistency not broken (one FK is always null), the delete should be successful. — Anton Putau, Sep 11 '15 at 20:41

score 2 · Accepted Answer · edited May 23 '17 at 11:51

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

Delete rooms
Delete images
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

I am expect only suggest but you already provide code example - thanks! — Anton Putau, Sep 12 '15 at 09:01

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

1 Answers1