CODEWITHSUNDEEP
sqlsql-serverdatabaseentity-framework-6
Thomas Hahn
Thomas Hahn

Reputation: 332

DELETE statement conflicted in Database First

Let's say I have a database with table A and table B. B has a foreign key to table A, which does not allow nulls. When I try to delete and entity of A I want all references in table B to be removed as well. I try to do this with the following code:

using (var ctx = new MyDatabaseContext(ConnectionString))
{
    var a= new A() { IdA= idA};
    ctx.A.Attach(a);
    ctx.A.Remove(a);
    ctx.SaveChanges();
}

This results in the following error message:

Additional information: The DELETE statement conflicted with the REFERENCE constraint "FK_B_A". The conflict occurred in database "MyDatabase", table "dbo.B", column 'IdA'.

The statement has been terminated.

I have tried a lot, from using Triggers in the database to defining the ON DELETE CASCADE, but Entity Framework does fail. What am I doing wrong?

Trigger:

ALTER TRIGGER [dbo].[trg_DelA]
ON [dbo].[A]
FOR DELETE AS
  BEGIN
    DELETE FROM B WHERE B.IdA = IdA;
  END

BTW: This is just an example. The actual database is larger and also contains intermediate tables for many to many relationships.

BR Thomas

Upvotes: 0

Views: 178

Answers (2)

Andrey Korneyev
Andrey Korneyev

Reputation: 26876

AFTER (or FOR - they are synonyms) trigger are fired after triggering SQL statement. In your case this is too late, since deleting statement can't be completed due to foreign keys.

If you want to use trigger to handle cascade deletion - you have to use instead of trigger, and in this trigger first delete records from B table and then from A table.

So this could look like:

CREATE TRIGGER [dbo].[trg_DelA]
ON [dbo].[A]
INSTEAD OF DELETE AS
BEGIN
    DELETE FROM B WHERE B.IdA in (select IdA from deleted)
    DELETE FROM A WHERE IdA in (select IdA from deleted)
END

See MSDN for reference.

Upvotes: 2

Kemal Güler
Kemal Güler

Reputation: 614

Cascade your deletes.

Take a look at this: Entity framework code first delete with cascade

And this: https://msdn.microsoft.com/en-us/library/hh295843(v=vs.103).aspx

Upvotes: 1

Related Questions