Delete an entity with referential integrity constraints

Question

Let's say you have the following tables:

Friend
------
Id int not null (primary key)
Name nvarchar(50) not null

Address
-------
Id int not null (primary key)
FriendId int not null (links to Friend.Id)
City nvarchar(50) null
Country nvarchar(50) not null

Using Entity Framework 4, I am issuing an ObjectContext.ExecuteStoreCommand call to delete some friends. However, I get an error that says something like:

*System.Data.SqlClient.SqlException was unhandled Message=The DELETE statement conflicted with the REFERENCE constraint "FK_Address_Friend". The conflict occurred in database "MyFriends", table "dbo.Address", column 'FriendId'. The statement has been terminated.*

How do I ensure that everything Cascade deletes? Is there some property in the database that I have to set?

Answer 1

It sounds like you don't have the cascades set up properly on your SQL FK's. Possibly you have the cascade settings set to none? If you're not using the framework to do the delete (and possibly even if you are) then SQL server has to be configured to cascade the deletes.