SQL 与 FOREIGN KEY 约束冲突 [英] SQL conflicted with the FOREIGN KEY constraint

问题描述

我正在尝试在我的数据库上运行一些更新脚本，但出现以下错误:

I am trying to run some update scripts on my database and I am getting the following error:

ALTER TABLE 语句与 FOREIGN KEY 约束FK_UPSELL_DT_AMRNO_AFMKTG_REF"冲突.冲突发生在数据库ECOMVER"、表dbo.AFFILIATE_MKTG_REF"、AMRNO"列中.

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 [dbo].[UPSELL_DATA]  WITH CHECK ADD 
        CONSTRAINT [FK_UPSELL_DT_AMRNO_AFMKTG_REF] FOREIGN KEY
        (
          [AMRNO]
        ) REFERENCES [dbo].[AFFILIATE_MKTG_REF] (
          [AMRNO]
        )
GO

AMRNO 是表 AFFILIATE_MKTG_REF 中的一个 PK.

AMRNO is a PK in table AFFILIATE_MKTG_REF.

此外，我尝试使用 SQL Management Studio 中的修改表选项创建外键关系，但遇到了同样的错误.我不确定我应该寻找什么?

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?

任何建议将不胜感激.

推荐答案

您的 [dbo].[UPSELL_DATA] 表中可能有记录，其中 [AMRNO] 列中的值在 [dbo] 中不存在.[AFFILIATE_MKTG_REF] 表，[AMRNO] 列.尝试这样的查询以查找那些没有匹配记录的查询:

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:

select   *
from     [dbo].[UPSELL_DATA] u
left join [dbo].[AFFILIATE_MKTG_REF] m
on       u.AMRNO = m.AMRNO
where    m.AMRNO is null

这篇关于SQL 与 FOREIGN KEY 约束冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！