Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.


share|improve this question
up vote 1 down vote accepted

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,
 Other varchar(10));
 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');
      CONSTRAINT FK_Parent
      FOREIGN    KEY (ParID)
      REFERENCES Hier(ID);
INSERT INTO dbo.Hier (ParID, Other) VALUES (3, 'Delta');
SELECT * FROM dbo.Hier;
DROP TABLE dbo.Hier;

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

share|improve this answer
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 posting your answer, you agree to the privacy policy and terms of service.

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