SQL Server 2019：如何实现两列全局唯一与同行列值不重复？

一、确保单行内两个ID不重复 #

直接给表添加CHECK约束，保证同一行的sourceLocationId和destinationLocationId永远不同：

-- PostgreSQL、SQL Server、MySQL 8.0.16+ 均支持
ALTER TABLE 你的表名
ADD CONSTRAINT chk_diff_locations CHECK (sourceLocationId != destinationLocationId);

二、实现两列全局唯一（任何值不能出现在source或destination的任意行） #

普通唯一约束无法满足这个需求，可通过以下两种方式实现：

方法1：辅助表+外键约束 #

1. 创建存储所有合法LocationId的辅助表，通过主键保证唯一性：

CREATE TABLE LocationIds (
    locationId INT PRIMARY KEY -- 类型需与原表ID类型一致
);

2. 修改原表，让两个字段都外键关联辅助表，同时保留上述CHECK约束：

ALTER TABLE 你的表名
ADD CONSTRAINT fk_source_location FOREIGN KEY (sourceLocationId) REFERENCES LocationIds(locationId),
ADD CONSTRAINT fk_destination_location FOREIGN KEY (destinationLocationId) REFERENCES LocationIds(locationId);

这样所有要插入的source或destination ID必须先存在于辅助表中，且同一个ID只能被使用一次——无论作为source还是destination。

方法2：触发器检查 #

若不想用辅助表，可通过触发器在插入/更新时校验全局唯一性：

以MySQL为例：

-- 插入前校验
DELIMITER //
CREATE TRIGGER check_global_unique_insert BEFORE INSERT ON 你的表名
FOR EACH ROW
BEGIN
    IF EXISTS (SELECT 1 FROM 你的表名 WHERE sourceLocationId = NEW.destinationLocationId OR destinationLocationId = NEW.sourceLocationId) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该LocationId已在另一列中使用';
    END IF;
    IF NEW.sourceLocationId = NEW.destinationLocationId THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'source和destination不能相同';
    END IF;
END //
DELIMITER ;

-- 更新前校验
DELIMITER //
CREATE TRIGGER check_global_unique_update BEFORE UPDATE ON 你的表名
FOR EACH ROW
BEGIN
    -- 排除当前行自身，避免更新时误判
    IF EXISTS (SELECT 1 FROM 你的表名 WHERE (sourceLocationId = NEW.destinationLocationId OR destinationLocationId = NEW.sourceLocationId) AND id != NEW.id) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该LocationId已在另一列中使用';
    END IF;
    IF NEW.sourceLocationId = NEW.destinationLocationId THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'source和destination不能相同';
    END IF;
END //
DELIMITER ;

注意：不同数据库的触发器语法有差异，但核心逻辑都是在写入前检查目标值是否已在两列的全局范围内存在。

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