SQL ALTER TABLE 语句与 FOREIGN KEY 约束冲突

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

提问by user3162932

I have a problem when trying to add a foreign key to my tblDomaretable; what am I doing wrong here?

尝试向tblDomare表中添加外键时遇到问题；我在这里做错了什么？

CREATE TABLE tblDomare
(PersNR VARCHAR (15) NOT NULL,
fNamn VARCHAR (15) NOT NULL,
eNamn VARCHAR (20) NOT NULL,
Erfarenhet VARCHAR (5),
PRIMARY KEY (PersNR));

INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet)
Values (6811034679,'Bengt','Carlberg',10);

INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet)
Values (7606091347,'Josefin','Backman',4);

INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet)
Values (8508284163,'Johanna','Backman',1);

CREATE TABLE tblBana
(BanNR VARCHAR (15) NOT NULL,
PRIMARY KEY (BanNR));

INSERT INTO tblBana (BanNR)
Values (1);

INSERT INTO tblBana (BanNR)
Values (2);

INSERT INTO tblBana (BanNR)
Values (3);

ALTER TABLE tblDomare
ADD FOREIGN KEY (PersNR)
REFERENCES tblBana(BanNR);

Error message:

错误信息：

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_tblDomare_PersN__5F7E2DAC". The conflict occurred in database "almu0004", table "dbo.tblBana", column 'BanNR'.

ALTER TABLE 语句与 FOREIGN KEY 约束“FK_ tblDomare_PersN__5F7E2DAC”冲突。冲突发生在数据库“almu0004”、表“dbo.tblBana”、“BanNR”列中。

回答by Smutje

It occurred because you tried to create a foreign key from tblDomare.PersNRto tblBana.BanNRbut/and the values in tblDomare.PersNRdidn't match with any of the values in tblBana.BanNR. You cannot create a relation which violates referential integrity.

发生这种情况是因为您尝试从 to 创建外键tblDomare.PersNR，tblBana.BanNR但是/并且 中的值tblDomare.PersNR与 中的任何值都不匹配tblBana.BanNR。您不能创建违反参照完整性的关系。

回答by dantey89

This query was very useful for me. It shows all values that don't have any matches

这个查询对我非常有用。它显示没有任何匹配项的所有值

select FK_column from FK_table
WHERE FK_column NOT IN
(SELECT PK_column from PK_table)

回答by PatsonLeaner

Try this solution:

试试这个解决方案：

There is a data item in your table whose associated value doesn't exist in the table you want to use it as a primary key table. Make your table empty or add the associated value to the second table.

您的表中有一个数据项，其关联值在您要用作主键表的表中不存在。使您的表为空或将关联的值添加到第二个表中。

回答by Ankita Biswas

It is possible to create the foreign key using ALTER TABLE tablename WITH NOCHECK ..., which will allow data that violates the foreign key.

可以使用 ALTER TABLE tablename WITH NOCHECK ... 创建外键，这将允许违反外键的数据。

"ALTER TABLE tablename WITH NOCHECK ..." option to add the FK -- This solution worked for me.

添加 FK 的“ALTER TABLE tablename WITH NOCHECK ...”选项——这个解决方案对我有用。

回答by sam05

I guess, a column value in a foreign key table should match with the column value of the primary key table. If we are trying to create a foreign key constraint between two tables where the value inside one column(going to be the foreign key) is different from the column value of the primary key table then it will throw the message.

我猜，外键表中的列值应该与主键表的列值匹配。如果我们试图在两个表之间创建外键约束，其中一列（将成为外键）内的值与主键表的列值不同，那么它将抛出消息。

So it is always recommended to insert only those values in the Foreign key column which are present in the Primary key table column.

因此，始终建议仅在外键列中插入存在于主键表列中的那些值。

For ex. If the Primary table column has values 1, 2, 3 and in Foreign key column the values inserted are different, then the query would not be executed as it expects the values to be between 1 & 3.

例如。如果主表列的值为 1、2、3，并且在外键列中插入的值不同，则不会执行查询，因为它期望值介于 1 和 3 之间。

回答by GirishBabuC

Before You add Foreign key to the table, do the following

在向表中添加外键之前，请执行以下操作

1. Make sure the table must empty or The column data should match.
2. Make sure it is not null.

3. If the table contains do not go to design and change, do it manually.

   alter table Table 1 add foreign key (Column Name) references Table 2 (Column Name)

   alter table Table 1 alter column Column Name attribute not null

1. 确保表必须为空或列数据应匹配。
2. 确保它不为空。

3. 如果表包含不要去设计和更改，请手动进行。

   alter table 表 1 添加外键（列名）引用表 2（列名）

   更改表表 1 更改列列名称属性不为空

回答by max

Clean your data from your tables and then make relation between them.

从表中清除数据，然后在它们之间建立关系。

回答by Thinker Bell

Try DELETEthe current datas from tblDomare.PersNR. Because the values in tblDomare.PersNRdidn't match with any of the values in tblBana.BanNR.

尝试DELETE来自 的当前数据tblDomare.PersNR。因为 中的值tblDomare.PersNR与 中的任何值都不匹配tblBana.BanNR。

回答by ako

i had this error too as Smutje reffered make sure that you have not a value in foreign key column of your base foreign key table that is not in your reference table i.e(every value in your base foreign key table(value of a column that is foreign key) must also be in your reference table column) its good to empty your base foreign key table first then set foreign keys

我也有这个错误，因为 Smutje reffered 确保你的基本外键表的外键列中没有你的参考表中没有的值，即（你的基本外键表中的每个值（一个列的值是外键）也必须在您的参考表列中）最好先清空基本外键表然后设置外键

回答by khadar

the data you have entered a table(tbldomare) aren't match a data you have assigned primary key table. write between tbldomare and add this word (with nocheck) then execute your code.

您输入表 (tbldomare) 的数据与您已分配主键表的数据不匹配。在 tbldomare 之间写入并添加这个词（使用 nocheck）然后执行您的代码。

for example you entered a table tbldomar this data

例如你输入了一个表 tbldomar 这个数据

INSERT INTO tblDomare (PersNR,fNamn,eNamn,Erfarenhet)
Values (6811034679,'Bengt','Carlberg',10);

and you assigned a foreign keytable to accept only 1,2,3.

并且您分配了一个foreign key表只接受1,2,3.

you have two solutions one is delete the data you have entered a table then execute the code. another is write this word (with nocheck) put it between your table name and add like this

您有两种解决方案，一种是删除您输入表中的数据，然后执行代码。另一个是写这个词（没有检查）把它放在你的表名之间并像这样添加

ALTER TABLE  tblDomare with nocheck
ADD FOREIGN KEY (PersNR)
REFERENCES tblBana(BanNR);