与外键约束冲突

I have two tables

我有两个表

ACADEMIE:

ACADEMIE:

CREATE TABLE [dbo].[R_ACADEMIE](
    [ID_ACADEMIE] [dbo].[IDENTIFIANT] NOT NULL,
    [LC_ACADEMIE_CODE] [dbo].[LIBELLE_COURT] NOT NULL,
    [LM_ACADEMIE_LIBELLE] [dbo].[LIBELLE_MOYEN] NOT NULL,
 CONSTRAINT [PK_R_ACADEMIE] PRIMARY KEY NONCLUSTERED 
(
    [ID_ACADEMIE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

I add my column in my E_VOYAGEUR table

我在E_VOYAGEUR表中添加列

ALTER TABLE E_VOYAGEUR ADD ID_ACADEMIE IDENTIFIANT NOT NULL DEFAULT 0;

I want to create a FK on my E_VOYAGEUR table i do this:

我想在我的E_VOYAGEUR表上创建一个FK我这样做:

ALTER TABLE [dbo].[E_VOYAGEUR]  WITH CHECK ADD  CONSTRAINT [FK_E_VOYAGE_A_VOYAGEU_E_ACADEMIE] FOREIGN KEY([ID_ACADEMIE])
REFERENCES [dbo].[R_ACADEMIE] ([ID_ACADEMIE])
GO

i have this error:

我有这个错误:

The ALTER TABLE statement is in conflict with the FOREIGN KEY constraint " FK_E_VOYAGE_A_VOYAGEU_E_ACADEMIE " . The conflict occurred in database " DEV ", " dbo.R_ACADEMIE " table, column ' ID_ACADEMIE ' .

ALTER TABLE语句与外键约束“FK_E_VOYAGE_A_VOYAGEU_E_ACADEMIE”冲突。冲突发生在数据库“DEV”、“dbo”中。“R_ACADEMIE”表，列“ID_ACADEMIE”。

1 个解决方案

#1

Seems your table E_VOYAGEUR already having some values And you are altering that table to add the new column ID_ACADEMIE with default value as 0. This makes all the rows in the table E_VOYAGEUR with 0 value for the columnn ID_ACADEMIE . And when you are adding with the foreigh key constraint to that table ,reference table, [R_ACADEMIE] may not have an entry with value '0' in the column ID_ACADEMIE and that seems to be the cause of this issue.

似乎您的表E_VOYAGEUR已经有一些值，并且您正在修改该表以添加默认值为0的新列ID_ACADEMIE。这使得表中的所有行E_VOYAGEUR为columnid_academie的值为0。当您添加了foreigh键约束到该表时，参考表，[R_ACADEMIE]在列ID_ACADEMIE中可能没有具有值'0'的条目，这似乎是导致这个问题的原因。

Before adding the foreign key to the table E_VOYAGEUR,make sure the value in the column ID_ACADEMIE having references in the table [R_ACADEMIE] .

在向表E_VOYAGEUR添加外键之前，请确保ID_ACADEMIE列中的值在表[R_ACADEMIE]中具有引用。