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?

Answer 1

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.