I have been working on something for some time now and struggling. We have our database deployments fully automated with Schema and Data comparisons but every once in a while we run into an issue where we get an UPDATE error on a Forign Key constraint. The error is as follows:
Exception of type System.Data.SqlClient.SqlException was thrown. Details:
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Menu_Item_Page". The conflict occurred in database "DCP_Membership", table "dbo.Page", column 'Page_Key'.
This ocurrs becuase there was a record added to the Page table that the Menu_Item table references, however that Insert has not been executed yet. When debugging, I see that the order of tables that we get back from Red-Gate is out of order in how these records need to be updated.
Is there any way around this or am I missing something simple? Seems like this would be an issue for many here on the forum but I have not found anything regarding it.