SQL Server添加外键报Msg1776无匹配主键候选键错误排查

现有表结构与问题复现 #

现有两张业务表，建表语句如下：

create table sales.SpecialOfferProduct 
(
    SpecialOfferID int not null,
    ProductID int not null,
    rowguid uniqueidentifier not null,
    ModifiedDate datetime not null,
    primary key (specialofferid, productid)
)

create table sales.SalesOrderDetail 
(
    SalesOrderID int not null,
    SalesOrderDetailId int not null,
    CarrierTrackingNumber nvarchar(25),
    OrderQty smallint not null,
    ProductId int not null,
    SpecialOfferId int not null,
    UnitPrice money not null,
    UnitPriceDiscount money not null,
    LineTotal as (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty], (0.0))),
    rowguid uniqueidentifier not null,
    ModifiedDate datetime not null,
    primary key (SalesOrderID, SalesOrderDetailId)
)

执行如下SQL尝试为sales.SalesOrderDetail表添加外键，关联sales.SpecialOfferProduct表的ProductId字段：

alter table sales.SalesOrderDetail
    add foreign key (ProductId) 
        references sales.SpecialOfferProduct(ProductId)

执行后返回错误：

Msg 1776, Level 16, State 0, Line 180
被引用表'sales.SpecialOfferProduct'中不存在与外键'FK__SalesOrde__Produ__4E88ABD4'的引用列列表匹配的主键或候选键。

Msg 1750, Level 16, State 1, Line 180
无法创建约束或索引，请查看前面的错误信息。

错误根因 #

SQL Server对外键约束有强制要求：外键引用的目标列，必须是被引用表的主键，或是被唯一约束、唯一索引覆盖的候选键，要求目标列的值全局唯一可匹配。
查看SpecialOfferProduct表结构可知，该表主键是(SpecialOfferID, ProductID)组成的联合主键，只有两个字段组合后的值才唯一，单独的ProductID字段既不属于单字段主键，也没有配置单独的唯一约束/唯一索引，同一个ProductID可以关联多个不同的SpecialOfferID，列值可重复，不满足外键引用的前置要求，因此创建单字段外键时会触发报错。

解决方法 #

根据实际业务逻辑选择对应方案即可：

• 方案1（推荐，匹配现有表设计语义）：创建联合外键
  绝大多数业务场景下，销售订单明细和特价商品的关联本身就需要同时匹配「特价活动ID+商品ID」两个维度，同一个商品可以参与多个不同的特价活动，这种场景下需要创建两字段的联合外键，对应操作步骤：
  1. 先排查脏数据，确认SalesOrderDetail中现存的(SpecialOfferId, ProductId)组合都在SpecialOfferProduct表中存在，否则外键创建会失败：

     SELECT sod.SpecialOfferId, sod.ProductId
     FROM sales.SalesOrderDetail sod
     LEFT JOIN sales.SpecialOfferProduct sop
       ON sod.SpecialOfferId = sop.SpecialOfferID AND sod.ProductId = sop.ProductID
     WHERE sop.SpecialOfferID IS NULL;
     

  2. 清理掉查询返回的不匹配脏数据后，执行语句创建联合外键：

     ALTER TABLE sales.SalesOrderDetail
     ADD CONSTRAINT FK_SalesOrderDetail_SpecialOfferProduct
       FOREIGN KEY (SpecialOfferId, ProductId)
       REFERENCES sales.SpecialOfferProduct(SpecialOfferID, ProductID);
     

• 方案2（仅适配特殊业务需求）：为目标列加唯一约束后创建单字段外键
  如果业务逻辑确实要求单独以ProductID作为外键关联，需要先给SpecialOfferProduct表的ProductID字段添加唯一约束，保证列值唯一，再创建外键：
  1. 先检查SpecialOfferProduct表中ProductID是否存在重复值，有重复则无法创建唯一约束：

     SELECT ProductID, COUNT(*) AS repeat_count
     FROM sales.SpecialOfferProduct
     GROUP BY ProductID
     HAVING COUNT(*) > 1;
     

  2. 确认无重复值后，给ProductID添加唯一约束：

     ALTER TABLE sales.SpecialOfferProduct
     ADD CONSTRAINT UQ_SpecialOfferProduct_ProductID UNIQUE(ProductID);
     

  3. 执行原外键创建语句即可。

内容的提问来源于stack exchange，提问作者Ann Ostrovsky