0

I have a SQL association table that has just two columns which are both foreign keys (ID's of twos from two other tables) that represents a relationship between two data objects. It seems natural that those columns have a NOT NULL constraint, because it would seem meaningless to have a relationship between an object and null, or between null and null.

What surprises me, is that when my (WPF, LINQ, C#) program tries to clear an object's associations, the following submit raises an exception saying one of the columns can't be null. I don't want it to be null, I want the whole row to be cleared.

Now, I could drop the null constraint to bypass the exception, but that seems like it shouldn't be necessary.

Edit: actually, SQL Server won't let me do only that, because the two values are the primary key, and a null isn't allowed in a primary key, which makes sense ... but again, I'm trying to remove rows, not set their ID's to null.

So I ask:

Why is removing rows (via clear()) resulting in an exception about a null column value?

and

Is there some other way for me to clear such rows that I should be doing, or do I need to remove the not null constraint?

Or is there something else I am not understanding?

Details:

My table has two columns:

RecipeID (PK, FK, int, not null)
AltRecipeID (PK, FK, int, not null)

C# LINQ-to-SQL code:

RecipeToUpdate.PFW_AlternateRecipes.Clear();
App.db.SubmitChanges();

Exception on submit:

An attempt was made to remove a relationship between a PFW_Recipe and a PFW_AlternateRecipe. However, one of the relationship's foreign keys (PFW_AlternateRecipe.RecipeID) cannot be set to null.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
Dronz
  • 1,970
  • 4
  • 27
  • 50
  • 1
    http://stackoverflow.com/questions/2569119/linq-to-sql-and-many-to-many-relationship-deletions – BNL Aug 22 '12 at 20:29
  • 1
    http://stackoverflow.com/questions/900919/how-do-i-delete-records-from-a-child-collection-in-linq-to-sql – BNL Aug 22 '12 at 20:31
  • Thanks BNL. Those are interesting, but don't explain enough detail about what to do for me to take the plunge. The first link seems most relevant, but it also says there is trouble if the database does not have a cascading delete rule, and mine does not. And it doesn't say exactly how to set all that up, and it would require schema changes. I would do it if it made complete sense to me and I knew what to do, but I don't. I do see in SQL Mgmt Studio I could set the delete rule to CASCADE. – Dronz Aug 22 '12 at 21:55
  • However, not content with that, I decided to look at my own DB and code further, and I see I have several other tables like this, none of them have a cascading delete rule set, and I can and do remove such rows from this and other tables without getting exceptions. So I switched the code from "RecipeToUpdate.PFW_AlternateRecipes.Clear();" to " foreach (PFW_AlternateRecipe altRcp in RecipeToUpdate.PFW_AlternateRecipes) { App.db.PFW_AlternateRecipes.DeleteOnSubmit(altRcp); }" and that works with no problem. I have no clear idea why. Anyone know? – Dronz Aug 22 '12 at 21:56

0 Answers0