Wednesday, April 25, 2012

MVC SQL cyclical cascade paths

I'm building a simple code-first MVC 3 blog like application. My model has three tables, user, comment, and tutorial. The comment table has a FK for both user and tutorial. Tutorial has also a foreign key for user.

When I run the program I get the following error:

Introducing FOREIGN KEY constraint 'FK_Comments_Users_UserID' on table 'Comments' 
may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.

It seems like the problem is being caused by something called cascading deletes. Microsoft's solution to this is to have better DB design, But this DB seems to be about as simple as you can get.

I've found a few other posts (Entity Framework Code first - FOREIGN KEY constraint problem) which seem to address this issue by adding a the following code in my db context class:
protected override void OnModelCreating(DbModelBuilder modelBuilder)

.HasMany(u => u.Comments)
.HasRequired(c => c.User)
.HasForeignKey(c => c.UserId)

Unfortunately, Visual Studio gets mad at the syntax at '.HasRequired' The actual error is "Error 1 'System.Data.Entity.ModelConfiguration.Configuration.ManyNavigationPropertyConfiguration' does not contain a definition for 'HasRequired' and no extension method 'HasRequired' accepting a first argument of type 'System.Data.Entity.ModelConfiguration.Configuration.ManyNavigationPropertyConfiguration' could be found (are you missing a using directive or an assembly reference?)"

It seems like there is something simple here that I am missing. How can I restructure my database better or how can I solve this cyclical redundancy problem?

No comments:

Post a Comment