如何为customers表添加约束限制primary_number与secondary_number跨列重复？

你的需求是确保primary_number和secondary_number两列之间没有重复号码——即任何号码不能同时出现在这两个列中，以下是针对不同数据库的实现方案：

方案1：使用检查约束（适用于PostgreSQL、SQL Server、Oracle等支持行级检查约束的数据库） #

通过两个检查约束分别验证两列的互斥性：

-- 限制secondary_number不能出现在primary_number列中
ALTER TABLE customers
ADD CONSTRAINT chk_secondary_not_in_primary
CHECK (secondary_number NOT IN (SELECT primary_number FROM customers));

-- 限制primary_number不能出现在secondary_number列中
ALTER TABLE customers
ADD CONSTRAINT chk_primary_not_in_secondary
CHECK (primary_number NOT IN (SELECT secondary_number FROM customers));

注意：如果字段允许为NULL，上述约束会自动忽略空值（因NOT IN包含NULL时会返回UNKNOWN，约束不触发）；若需禁止空值，需额外添加NOT NULL约束。

方案2：使用触发器（适用于MySQL 8.0.16之前版本等不支持检查约束的数据库） #

通过插入和更新触发器来拦截违规数据：

插入前验证触发器 #

DELIMITER //
CREATE TRIGGER trg_customers_insert_check
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
    -- 检查新的primary_number是否已存在于secondary_number列
    IF EXISTS (SELECT 1 FROM customers WHERE secondary_number = NEW.primary_number) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'primary_number不能存在于secondary_number列中';
    END IF;
    -- 检查新的secondary_number是否已存在于primary_number列
    IF EXISTS (SELECT 1 FROM customers WHERE primary_number = NEW.secondary_number) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'secondary_number不能存在于primary_number列中';
    END IF;
END //
DELIMITER ;

更新前验证触发器 #

DELIMITER //
CREATE TRIGGER trg_customers_update_check
BEFORE UPDATE ON customers
FOR EACH ROW
BEGIN
    -- 排除当前行旧值，检查更新后的primary_number是否违规
    IF EXISTS (SELECT 1 FROM customers WHERE secondary_number = NEW.primary_number AND id != NEW.id) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'primary_number不能存在于secondary_number列中';
    END IF;
    -- 排除当前行旧值，检查更新后的secondary_number是否违规
    IF EXISTS (SELECT 1 FROM customers WHERE primary_number = NEW.secondary_number AND id != NEW.id) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'secondary_number不能存在于primary_number列中';
    END IF;
END //
DELIMITER ;

方案3：辅助表+触发器（高效的大规模数据场景） #

通过维护一个存储所有唯一号码的辅助表，利用主键唯一性强制约束，性能优于检查约束：

-- 创建存储唯一号码的辅助表
CREATE TABLE customer_numbers (number VARCHAR(20) PRIMARY KEY);

-- 插入时同步数据的触发器
DELIMITER //
CREATE TRIGGER trg_sync_numbers_insert
AFTER INSERT ON customers
FOR EACH ROW
BEGIN
    IF NEW.primary_number IS NOT NULL THEN
        INSERT INTO customer_numbers VALUES (NEW.primary_number);
    END IF;
    IF NEW.secondary_number IS NOT NULL THEN
        INSERT INTO customer_numbers VALUES (NEW.secondary_number);
    END IF;
END //

-- 更新时同步数据的触发器
CREATE TRIGGER trg_sync_numbers_update
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
    -- 处理primary_number变更
    IF OLD.primary_number != NEW.primary_number THEN
        DELETE FROM customer_numbers WHERE number = OLD.primary_number;
        IF NEW.primary_number IS NOT NULL THEN
            INSERT INTO customer_numbers VALUES (NEW.primary_number);
        END IF;
    END IF;
    -- 处理secondary_number变更
    IF OLD.secondary_number != NEW.secondary_number THEN
        DELETE FROM customer_numbers WHERE number = OLD.secondary_number;
        IF NEW.secondary_number IS NOT NULL THEN
            INSERT INTO customer_numbers VALUES (NEW.secondary_number);
        END IF;
    END IF;
END //

-- 删除时同步数据的触发器
CREATE TRIGGER trg_sync_numbers_delete
AFTER DELETE ON customers
FOR EACH ROW
BEGIN
    DELETE FROM customer_numbers WHERE number = OLD.primary_number;
    DELETE FROM customer_numbers WHERE number = OLD.secondary_number;
END //
DELIMITER ;

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