locked
Deliberately orphaning records - but =The relationship could not be changed because one or more of the foreign-key properties is non-nullable RRS feed

  • Question

  • Hi folks,

    Hopefully this is a quick and easy one for someone with a bit of experience with EF. (Using 4.3.1 and Poco, if that matters)

    I've got an entity that I want to delete, but I DON'T want its foreign key references to be set to null...

    Why? Because the "referencing" data is a "history table", and although I want to delete the source record (the Order), I want to keep the "orphaned" ID in place and don't want to set it to null...

    I have a Mapping class that does this for my Order:

                this.HasMany(t => t.OrderHistory) // possibly needs to be in the "HIstory Mapping"
                    .WithRequired(t => t.Order)
                    .HasForeignKey(d => d.OrderId).WillCascadeOnDelete(false);

    which seems to do half the job but then when you delete you get this:

    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.

    Which is fine, but I don't WANT it set to null...

    History table looks like this (aproximately): HistoryId, OrderId, What, When, WhoBy...

    So for example I want to have a History record with an OrderId=100 - But the Order Table would no longer have this entry...

    From what I've played with EF doesn't seem to do this...

    Is there any way to "make it so", or do I just have do something like "flag the entry as deleted" and live with this limitation?


    • Edited by noJedi Wednesday, August 1, 2012 6:08 AM Minor clarification
    Wednesday, August 1, 2012 5:17 AM

Answers

  • Hi,

    The only way I know of to have this scenario to work is to tell SQL Server to not enforce foreign key constraints. You can do this in the properties of the foreign key in SQL Managemnt Studio. Assuming you are using SQL Server that is.

    I don't know if turning that off is a good idea or not for your scenario, but it is the only way I know of to achieve what you are trying to do.

    Wednesday, August 1, 2012 10:32 PM

All replies

  • Hi,

    The only way I know of to have this scenario to work is to tell SQL Server to not enforce foreign key constraints. You can do this in the properties of the foreign key in SQL Managemnt Studio. Assuming you are using SQL Server that is.

    I don't know if turning that off is a good idea or not for your scenario, but it is the only way I know of to achieve what you are trying to do.

    Wednesday, August 1, 2012 10:32 PM
  • Hi SmERIoN,

    Alternatively, you can create a new property (field) in your class (table) to store the same data OrderId has.

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, August 2, 2012 2:57 AM
  • It works, as you suggested... not sure if its a good idea either, but thanks for the info. :)
    Thursday, August 2, 2012 3:22 AM
  • Yeah, so many, many ways to skin a cat... as the saying goes.

    Gonna have to go back to the drawing board on this one and reassess everything.

    Thanks for suggestions.

    Thursday, August 2, 2012 3:42 AM