sql - FOREIGN KEY SAME TABLE error - but foreign key doesn't exist

Question

I am trying to add a foreign key constraint which references the same table.

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Documents_Parent]') AND parent_object_id = OBJECT_ID(N'[dbo].[Documents]'))
BEGIN
    ALTER TABLE [dbo].[Documents]  WITH CHECK ADD  CONSTRAINT [FK_Documents_Parent] FOREIGN KEY(LinkedDocumentId)
    REFERENCES [dbo].[Documents] ([Id])
END

The relationship FK_Documents_Parent does not exist.

However, it throws the error:

The ALTER TABLE statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_Documents_Parent". The conflict occurred in database "dev", table "dbo.Documents", column 'Id'.

Answer 1

If the table already has data, all values in the column documents_parent should be present in the column id, otherwise you will get an error.

You can use WITH NOCHECK, if we want to allow it

http://technet.microsoft.com/en-us/library/ms177463(v=sql.105).aspx