MySQL UNIQUE约束对含NULL外键的重复行失效问题求解

背景说明 #

现有三张表结构如下：

ProductVariant
----------------------------             
ProductVariantID  PK
ProductID         FK NOT NULL
VariantID         FK NULL
VariantValuesID   FK NULL

Variant
-----------------------------
VariantID         PK

VariantValues
------------------------------
VariantValuesID   PK       
VariantID         FK NOT NULL  

业务规则：

• 产品无变体时，ProductVariant表可插入VariantID和VariantValuesID均为NULL的行；
• 不允许仅其中一个字段为NULL，必须同时为NULL或同时为有效数值；
• 已添加UNIQUE约束ProdVarVarValUnique (ProductID, VariantID, VariantValuesID)，但该约束无法阻止ProductID相同且两个变体字段均为NULL的重复行插入。

解决方案（无需触发器） #

方案1：使用表达式唯一索引（推荐） #

利用数据库的表达式索引功能，将NULL替换为一个不会被业务使用的特殊值（比如-1，假设VariantID和VariantValuesID均为正整数主键），让UNIQUE约束能识别NULL的重复情况。

以SQL Server为例，创建唯一索引：

CREATE UNIQUE NONCLUSTERED INDEX IX_ProductVariant_Unique
ON ProductVariant (ProductID, COALESCE(VariantID, -1), COALESCE(VariantValuesID, -1));

优点：无需修改原表结构，性能优异，支持高并发，大部分主流数据库（SQL Server、PostgreSQL、MySQL 8.0+）均支持此类表达式索引。
注意：确保所选的替代值（如-1）不会出现在VariantID和VariantValuesID的实际业务数据中。

方案2：CHECK约束结合自定义函数 #

通过自定义函数检查同一ProductID下是否存在重复的双NULL行，再用CHECK约束限制插入。

首先创建检查函数：

CREATE FUNCTION dbo.CheckDuplicateNullProduct (@ProductID INT)
RETURNS BIT
AS
BEGIN
    DECLARE @DuplicateCount INT;
    SELECT @DuplicateCount = COUNT(*)
    FROM ProductVariant
    WHERE ProductID = @ProductID
      AND VariantID IS NULL
      AND VariantValuesID IS NULL;
    RETURN CASE WHEN @DuplicateCount > 1 THEN 1 ELSE 0 END;
END;

然后添加CHECK约束：

ALTER TABLE ProductVariant
ADD CONSTRAINT CK_ProductVariant_NoDuplicateNulls
CHECK (NOT (VariantID IS NULL AND VariantValuesID IS NULL AND dbo.CheckDuplicateNullProduct(ProductID) = 1));

缺点：函数查询存在性能损耗，高并发场景下可能出现竞态条件（比如两个事务同时插入时，函数查询的COUNT值可能滞后），仅适合低并发场景。

方案3：引入默认变体行+修改字段为NOT NULL #

在Variant和VariantValues表中插入代表“无变体”的默认行，将ProductVariant的两个变体字段改为NOT NULL，彻底避免NULL带来的约束问题，同时保留业务规则。

步骤如下：

1. 插入默认行（假设用0作为默认ID，确保不与现有主键冲突）：

INSERT INTO Variant (VariantID) VALUES (0);
INSERT INTO VariantValues (VariantValuesID, VariantID) VALUES (0, 0);

2. 修改ProductVariant表字段为NOT NULL：

ALTER TABLE ProductVariant
ALTER COLUMN VariantID INT NOT NULL;

ALTER TABLE ProductVariant
ALTER COLUMN VariantValuesID INT NOT NULL;

3. 添加CHECK约束保证两个字段同时为默认值或同时为有效业务值：

ALTER TABLE ProductVariant
ADD CONSTRAINT CK_ProductVariant_VariantPair
CHECK ((VariantID = 0 AND VariantValuesID = 0) OR (VariantID <> 0 AND VariantValuesID <> 0));

4. 保留原UNIQUE约束（已存在可跳过）：

ALTER TABLE ProductVariant 
ADD CONSTRAINT ProdVarVarValUnique UNIQUE (ProductID, VariantID, VariantValuesID);

优点：完全符合关系模型规范，NULL问题彻底解决，约束逻辑清晰可靠，无性能隐患。
缺点：需要修改表结构和插入默认数据，适合新系统或可接受数据迁移的场景。

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