CODEWITHSUNDEEP
sql-serverentity-frameworkdatabase-design
Adir
Adir

Reputation: 1423

SQL Server ON DELETE and inheritance

I have 3 tables: Notifications, NewItemNotifications and Items.
I've set an ON DELETE rule on the NewItemNotifications and Items table which deletes the NewItemNotification row when I delete some item.
The problem is that the parent row in the Notifications table is still exists, how can I handle this?

enter image description here

Upvotes: 0

Views: 562

Answers (1)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364369

NewItemNotification is dependent table - it can never trigger deletion of parent record through database constraint. The only way is to write a database trigger on NewItemNotification to perform delete in Notification table after the dependent record is deleted.

The problem is that such trigger can cause issue if EF will try to delete NewItemNotification because it doesn't know about trigger existence. It first deleted NewItemNotification record which trigger deletion of Notification item without EF to know it. EF will then try to delete Notification record again but the record was already deleted. I think it will result in concurrency exception.

The best option in this case is not using cascade delete and handle delete yourselves.

Upvotes: 2

Related Questions