0

I am trying to remove all data from a table and re-add it, but I get the below exception:

An exception of type 'System.InvalidOperationException' occurred in EntityFramework.dll but was not handled in user code Additional information: The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.

At first I though it was pretty obvious, until I looked at my database (below). To check if I was missing something I deleted all relevant rows in SSMS successfully. If I can delete the rows without foreign key constraints in SQL, why cant I do it in EF?

The relevant tables

EDIT: I am trying to delete from the rooms table

tony09uk
  • 2,841
  • 9
  • 45
  • 71
  • Which table are you deleting from? – Paul Abbott Dec 30 '15 at 23:20
  • Sorry, I should have mentioned that I have updated my question – tony09uk Dec 30 '15 at 23:28
  • 1
    the error message is clear: When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined. It means that you can't remove data from table because of the foreign key constraints. You either remove all data from the others table first, or set all fks as nullable – Fabio Dec 31 '15 at 00:23
  • There is no data in the images table and the property table is not reliant on the rooms table, and if I run delete from Rooms where propertyID = [someID], it does not cause any issues. Hence my confusion over this message – tony09uk Dec 31 '15 at 10:00

1 Answers1

0

Based on the error message and that you are attempting to delete from the Rooms table, it seems likely that you have an enforced foreign key constraint on the Images table tied to RoomID.

You can determine what relationship is causing the issue by right-clicking the Rooms table in SSMS and clicking "Show Dependencies". This will show you all of the schema-bound dependencies which will be based on foreign keys.

Assuming that is the case, deleting those rows from Rooms would orphan the records in Images or potentially tie them to the records you are inserting (if doing an identity insert), not certain what your intention is. You have two options if you wish to proceed:

  • Delete the records constrained by the foreign key.
  • Disable the enforcement of the foreign key in SSMS by right-clicking the foreign key, click Modify, and set "Enforce Foreign Key Contraint" to "No". You may also need to set the Insert and Update specifications to No Action.

Ideally you are just deleting some records and removing the constraint is not necessary, since that compromises the referential integrity of your database.

vanlee1987
  • 181
  • 1
  • 7
  • Thanks for your answer. The images table has no records, therefore orphan records would not be a problem, is this not recognised by EF? secondly why can I run `delete from Rooms where propertyID = [someID]` in ssms and face no problems? – tony09uk Dec 31 '15 at 10:04
  • I see, you're right, exception must be getting thrown by the framework. May help to post your code, but with what you have explained, I think this might be the answer: http://stackoverflow.com/questions/19325473/ef6-0-the-relationship-could-not-be-changed-because-one-or-more-of-the-foreign/19326114#19326114 – vanlee1987 Dec 31 '15 at 20:55
  • I decided to delete the records before updating, this worked, I'm not sure it's a solution though – tony09uk Dec 31 '15 at 23:49
  • You might try enable cascading delete in both the Entity Framework code (http://stackoverflow.com/questions/17487577/entity-framework-ef-code-first-cascade-delete-for-one-to-zero-or-one-relations) and in SSMS (http://stackoverflow.com/questions/4454861/how-do-i-edit-a-table-in-order-to-enable-cascade-delete). – vanlee1987 Jan 01 '16 at 03:12