but foreign key doesn't exist

2018-06-23 12:02:32

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'.

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

链接地址: http://www.djcxy.com/p/65890.html

上一篇: 不会指定一个外键约束让我陷入麻烦？

下一篇: 但外键不存在