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

Question

I have an SQL Server table structure where in some columns are foreign keys that refer to different look up tables. These columns are created as NOT NULL and with DEFAULT value = 1 as the look up value for ID = 1 is the default value I want to assign in case NULL is passed while saving a record.

However, I got below error while trying to Add/Update/Delete records of this child table.

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

I tried to search a lot but I got solution to change LINQ to SQL code or XML manually for resolving this. I cannot do that because in future when ever the table gets changed, I'll have to make sure the manual change is applied every time.

Also, I can not change my columns to accept NULL because that will impact other applications using the same table through LINQ To SQL.

I need more manageable solution for this.

Answer 1

I assume that you are trying to "reset" a reference to its default value by assigning null to it, expecting L2S to adopt the database default. However, as for L2S, you're only nullifying a foreign key. It's not aware of any defaults in the database.

So you have to change the reference by setting it to the default lookup object yourself. You could do that by adding a Reset() method to your classes, so you don't have to scatter this code all over the place.

Answer 2

I got this resolved by assigning default values to the Table class properties from Code it self. What I understood is this. LINQ to SQL does not take into consideration the default value set from the DB. Also, the column is NOT NULL. So being INT columns the default value that .NET assigned was 0 to these properties. Now, I don't have any master entry with ID = 0 in my database. Also, since the DB did not get a NULL value for this column, it does not attempt to apply the Default value at all.

So what I did was, before saving the record, if I find that if the control which is bound to this property is having a NULL value (Nothing is selected/ control is invisible etc) I assign the Default value from the code it self. This eliminates the need to fall back on the database to assign the default.

Now, everything is working fine. Thought to share this as I could not get any satisfactory answers from anywhere.