如何确保含NULL值的customer_id与sales_id组合的SQL唯一性约束生效？

默认的UNIQUE (customer_id, sales_id)约束管不了重复的(customer_id, NULL)组合，核心原因是SQL标准里NULL和任何值（包括另一个NULL）都不相等，数据库会把这类组合当成不同的记录，自然不会触发唯一约束校验。下面给几种实用的解决办法：

方案1：函数式唯一索引（适配多数主流数据库） #

把NULL值映射成一个不会和正常业务值冲突的固定值，让数据库能识别出重复的NULL组合，创建函数式唯一索引就行。

PostgreSQL示例： #

CREATE UNIQUE INDEX idx_logon_customer_sales_unique ON logon (
    customer_id, 
    COALESCE(sales_id, -1)  -- 把NULL换成-1，确保这个值不会出现在正常的sales_id里
);

MySQL（8.0+）示例： #

CREATE UNIQUE INDEX idx_logon_customer_sales_unique ON logon (
    customer_id,
    IFNULL(sales_id, -1)
);

SQL Server示例： #

CREATE UNIQUE NONCLUSTERED INDEX idx_logon_customer_sales_unique ON logon (
    customer_id,
    ISNULL(sales_id, -1)
);

提示：替换用的数值要选绝对不会在业务中出现的，比如sales_id都是正整数就用-1，要是有负数就换0或者一个超大值。

方案2：部分唯一索引（PostgreSQL专属） #

如果想分开处理：sales_id非NULL时校验(customer_id, sales_id)唯一，sales_id为NULL时只校验customer_id唯一，可以用部分索引：

-- 先保留原约束处理非NULL的情况
ALTER TABLE logon ADD CONSTRAINT uq_logon_customer_sales UNIQUE (customer_id, sales_id);
-- 再给NULL的情况单独加索引
CREATE UNIQUE INDEX idx_logon_customer_sales_null ON logon (customer_id) WHERE sales_id IS NULL;

方案3：触发器（适配所有数据库） #

要是你的数据库不支持函数式索引，就用触发器手动校验：

MySQL触发器示例： #

DELIMITER //
CREATE TRIGGER trg_logon_insert_unique_check BEFORE INSERT ON logon
FOR EACH ROW
BEGIN
    DECLARE duplicate_count INT;
    IF NEW.sales_id IS NULL THEN
        SELECT COUNT(*) INTO duplicate_count 
        FROM logon 
        WHERE customer_id = NEW.customer_id AND sales_id IS NULL;
        IF duplicate_count > 0 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '不能添加重复的(customer_id, NULL)组合';
        END IF;
    ELSE
        SELECT COUNT(*) INTO duplicate_count 
        FROM logon 
        WHERE customer_id = NEW.customer_id AND sales_id = NEW.sales_id;
        IF duplicate_count > 0 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '不能添加重复的(customer_id, sales_id)组合';
        END IF;
    END IF;
END //
DELIMITER ;

要是需要处理更新操作，再创建一个BEFORE UPDATE的触发器，逻辑和上面一致就行。

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