I am trying to establish a hierarchical relationship between rows in an existing table. There are two fields of interest: a not-nullable primary key field id, and a second nullable field parentId.
Both these fields are pre-existing, and the normal case is that parentId will be null. This seems to be a normal use case, and in fact we have some old tables using the same pattern.
I am trying to use fluentMigrator to add a foreign key constraint after the fact on these rows:
Create.ForeignKey("C", "ParentId", "C", "CId").OnUpdate(Rule.None);
It is failing with the error:
The ALTER TABLE statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_C_ParentId". The conflict occurred in database "R", table "dbo.C", column 'CId'.
This is not a problem with illegal values, as I did a bulk update on the test system, and set all
C.ParentId = null, and am still getting the same error.