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:

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'.

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.

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?

Any suggestions would be greatly appreciated.

---

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

---

我认为你有数据限制的外键尝试在分配外键之前检查两个表上的数据，无论这两个表是否有限制。

链接地址: http://www.djcxy.com/p/65882.html

上一篇: 视图上的TSQL外键？

下一篇: SQL与FOREIGN KEY约束冲突