Cascade Delete, same table, Entity Framework 4 Code First

Question

Hi Im currently working with .sdf database (Server Compact Version 4.0) and sql express. I'm trying to setup a cascade delete on a same table (category - sub category) but I get that I cant add relation to the same table.

A foreign key constraint had and update or a delete cascade rule, and self-references a column in the same table, is not allowed

What can I do about this?

EDIT
I'm the only one with this problem?

Answer 1

As your SQLException suggested, this is a limitation of SQL Server in general and has nothing to do with EF or Code First. Basically, SQL Server does not allow creating cascade actions on Inner relationships – when the cascade path goes from column col1 in table A to column col2 also in table A. A->A.

In fact, Code First was trying to use Declarative Referential Integrity (DRI) to enforce cascade deletes and SQL Server throws.

The only way to enforce cascade deletes for this relationship is to use Triggers. You can write a Delete Trigger on the category table that either deletes the dependent rows or sets all corresponding foreign keys to NULL (based on your requirements).