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.
Thoughts?
Best Answer
If there are no illegal values in C.ParentID, then I am not clear what the problem might be. Is this Microsoft SQL Server?
I created a test case as follows:
Seems to work fine. If you are on a different SQL Server it might have different behavior. Or fluentMigrator may have a problem.