SQL 与 FOREIGN KEY 约束冲突
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1758987/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL conflicted with the FOREIGN KEY constraint
提问by Brennan Mann
I am trying to run some update scripts on my database and I am getting the following error:
我正在尝试在我的数据库上运行一些更新脚本，但出现以下错误：
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_UPSELL_DT_AMRNO_AFMKTG_REF". The conflict occurred in database "ECOMVER", table "dbo.AFFILIATE_MKTG_REF", column 'AMRNO'.
ALTER TABLE 语句与 FOREIGN KEY 约束“FK_UPSELL_DT_AMRNO_AFMKTG_REF”冲突。冲突发生在数据库“ECOMVER”、表“dbo.AFFILIATE_MKTG_REF”、“AMRNO”列中。
I am running the following script:
我正在运行以下脚本：
ALTER TABLE [dbo].[UPSELL_DATA] WITH CHECK ADD
CONSTRAINT [FK_UPSELL_DT_AMRNO_AFMKTG_REF] FOREIGN KEY
(
[AMRNO]
) REFERENCES [dbo].[AFFILIATE_MKTG_REF] (
[AMRNO]
)
GO
AMRNO is a PK in table AFFILIATE_MKTG_REF.
AMRNO 是表 AFFILIATE_MKTG_REF 中的一个 PK。
Also, I tried to create the foreign key relation using the modify table option in SQL Management studio and I got the same error. I am not sure what I should be looking for?
此外，我尝试使用 SQL Management Studio 中的修改表选项创建外键关系，但遇到了同样的错误。我不确定我应该寻找什么？
Any suggestions would be greatly appreciated.
任何建议将不胜感激。
回答by boydc7
You probably have records in your [dbo].[UPSELL_DATA] table with values in the [AMRNO] column that don't exist in the [dbo].[AFFILIATE_MKTG_REF] table, [AMRNO] column. Try a query like this to find those that don't have matching records:
您的 [dbo].[UPSELL_DATA] 表中可能有记录，其中 [AMRNO] 列中的值在 [dbo].[AFFILIATE_MKTG_REF] 表 [AMRNO] 列中不存在。尝试这样的查询来查找那些没有匹配记录的查询：
select *
from [dbo].[UPSELL_DATA] u
left join [dbo].[AFFILIATE_MKTG_REF] m
on u.AMRNO = m.AMRNO
where m.AMRNO is null
回答by pranay
I think you have data restricted by foreign key try to check the data on both tables before assigning a foreign key, whether there are restrictions on both the tables.
我认为您有受外键限制的数据尝试在分配外键之前检查两个表上的数据，是否对两个表都有限制。
