The DELETE statement conflicted with the REFERENCE constraint in ASP.NET Dynamic Data

Question

I have two tables Application_User and Application_User_Access. Application_User_Access table is having a foreign key constraint with Application_User table.

When I delete a record in Application_User table, I receive "The DELETE statement conflicted with the REFERENCE constraint" exception.

This happens in ASP.NET Dynamic Data Entities Web application. I want to delete all the child records in this case and finally delete the parent record. How to implement this?

Solution

You can implement a cascading delete for Application_User_Access table. For this you need to modify your DB schema a little bit. Concretely remove the previous reference from the Application_User_Access to the Application_User table and add a new one:

--not sure about the column names though

ALTER TABLE Application_User_Access
ADD CONSTRAINT FK_Application_User_Access_Application_User
FOREIGN KEY (used_id)
REFERENCES Application_User(id)
ON DELETE CASCADE
GO

Notice that ON DELETE CASCADE thing. It means that whenever the primary key record is deleted the foreign key record referencing it will be removed as well.

Comments

[Anand]

In the partial class of my context, I added OnSavingChanges method with the below code,

[Andrei]

Well, this is a possible approach, but it has a little flaw. Application_User_Access records does not make sense without Application_User. This is not a business rule, it's a matter of the DB integrity. That's why I'd say that modifying the constraint by adding the cascade delete is a little bit better here. But after all you should go with the approach you find most comfortable.