无法创建外键，因为它与自身冲突 [英] Cannot create a foreign key becaue it is in conflict with itself

问题描述

Msg 547，Level 16，状态0，行1 ALTER TABLE语句
与FOREIGN KEY约束
FK_RDATA_COMBO_VALUES_ID_REFERENCES冲突。冲突发生在
数据库MyDatabase，表dbo.EVA_REFERENCES，列
'ID_REFERENCES'。

<这个错误信息说这个问题与 FK_RDATA_COMBO_VALUES_ID_REFERENCES 有冲突，但这是我正在创建的FK的名称，它还不存在。

这是我的表格：

pre $ CREATE TABLE [dbo]。[RDATA_COMBO_VALUES] （
[ID_RDATA_COMBO] [int] IDENTITY（1,1）NOT NULL，
[ID_REF_CDATA] [int] NOT NULL，
[ID_MODULE_REC_ID] [int] NOT NULL，
[VALUE] [nvarchar]（max）NULL，
[ID_REFERENCES] [INT] NOT NULL，
CONSTRAINT [PK_RDATA_COMBO_VALUES] PRIMARY KEY CLUSTERED
（
[ID_RDATA_COMBO] ASC
$）WITH [PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON] ON [PRIMARY]
）ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo]。[RDATA_CO MBO_VALUES] WITH CHECK ADD CONSTRAINT [FK_RDATA_COMBO_VALUES_ID_REF_CDATA] FOREIGN KEY（[ID_REF_CDATA]）
REFERENCES [dbo]。[EVA_REFERENCE_CDATA]（[ID_REF_CDATA]）
GO

ALTER TABLE [dbo ]。[RDATA_COMBO_VALUES] CHECK CONSTRAINT [FK_RDATA_COMBO_VALUES_ID_REF_CDATA]
GO

这是命令i用来创建FK：

$ pre $ AL $ TABLE ALTER TABLE [ ID_REFERENCES]）
REFERENCES [dbo]。[EVA_REFERENCES]（[ID_REFERENCES]）

ALTER TABLE 语句与FK冲突我还没有创建？

UPDATE：
this问题在这个中有一个延续，它引发了关于这个表格的所有问题。

解决方案

消息c应该是误导，但我想添加的FK与数据冲突，因为该字段包含的值不在被引用的表中。

在创建FK有必要删除孤儿。

I am trying to add a Foreignkey to a table and i cannot because i get this error:

Msg 547, Level 16, State 0, Line 1 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_RDATA_COMBO_VALUES_ID_REFERENCES". The conflict occurred in database "MyDatabase", table "dbo.EVA_REFERENCES", column 'ID_REFERENCES'.

The error message says that the problem is a conflict with FK_RDATA_COMBO_VALUES_ID_REFERENCES but this is the name of the FK i am trying to create, it does not exist yet.

This is my table:

CREATE TABLE [dbo].[RDATA_COMBO_VALUES](
    [ID_RDATA_COMBO] [int] IDENTITY(1,1) NOT NULL,
    [ID_REF_CDATA] [int] NOT NULL,
    [ID_MODULE_REC_ID] [int] NOT NULL,
    [VALUE] [nvarchar](max) NULL,
    [ID_REFERENCES] [int] NOT NULL,
 CONSTRAINT [PK_RDATA_COMBO_VALUES] PRIMARY KEY CLUSTERED 
(
    [ID_RDATA_COMBO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[RDATA_COMBO_VALUES]  WITH CHECK ADD  CONSTRAINT [FK_RDATA_COMBO_VALUES_ID_REF_CDATA] FOREIGN KEY([ID_REF_CDATA])
REFERENCES [dbo].[EVA_REFERENCE_CDATA] ([ID_REF_CDATA])
GO

ALTER TABLE [dbo].[RDATA_COMBO_VALUES] CHECK CONSTRAINT [FK_RDATA_COMBO_VALUES_ID_REF_CDATA]
GO

And this is the command i use to create the FK:

ALTER TABLE [dbo].[RDATA_COMBO_VALUES]  WITH CHECK ADD  CONSTRAINT [FK_RDATA_COMBO_VALUES_ID_REFERENCES] FOREIGN KEY([ID_REFERENCES])
REFERENCES [dbo].[EVA_REFERENCES] ([ID_REFERENCES])

Why the ALTER TABLE statements is in conflict with the FK i still not created?

UPDATE: this question has a continuation in this one that originated allmy questions about this table.

解决方案

The message could be misleading but the FK i am trying to add is in conflict with the data, because the field contains values that does not exist in the referenced table.

Before creating the FK it is necessary to remove orphans.

这篇关于无法创建外键，因为它与自身冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！