2

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?

2

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:

Create Table dbo.Hier
(Id  INT IDENTITY Primary KEy,
 ParID INT NULL,
 Other varchar(10));
GO
 INSERT INTO dbo.Hier (ParID, Other) VALUES (NULL, 'Able');
 INSERT INTO dbo.Hier (ParID, Other) VALUES (1, 'Baker');
 INSERT INTO dbo.Hier (ParID, Other) VALUES (NULL, 'Charlie');
GO
ALTER TABLE dbo.Hier ADD 
      CONSTRAINT FK_Parent
      FOREIGN    KEY (ParID)
      REFERENCES Hier(ID);
GO
INSERT INTO dbo.Hier (ParID, Other) VALUES (3, 'Delta');
SELECT * FROM dbo.Hier;
GO
DROP TABLE dbo.Hier;
GO

Seems to work fine. If you are on a different SQL Server it might have different behavior. Or fluentMigrator may have a problem.

  • This is microsoft SQL server, running locally with the database, and at the moment. There aren't any values at all, because I blew them all a way. Must be a migrator problem then, because if I drop the table and re-add, it builds just fine. Thanks for the quick response. – ElizabethH Sep 20 '13 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.