如何在MySQL中实现同类型列组合唯一，避免反向重复（禁用触发器）

问题背景 #

我有以下两张MySQL表：

CREATE TABLE `locations`(
    `location_id` INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`location_id`)
);

CREATE TABLE IF NOT EXISTS `routes`(
    `route_id` INT NOT NULL AUTO_INCREMENT,
    `location_id_1` INT NOT NULL,
    `location_id_2` INT NOT NULL,
    FOREIGN KEY (`location_id_1`) 
        REFERENCES `locations` (`location_id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY (`location_id_2`) 
        REFERENCES `locations` (`location_id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    UNIQUE KEY(`location_id_1`, `location_id_2`),
    PRIMARY KEY (`route_id`)
);

当前routes表的唯一键仅限制location_id_1和location_id_2的正向组合唯一——比如表中已有(1,2)时，无法再插入(1,2)，但(2,1)仍然可以插入。我需要实现：当存在(1,2)这类记录时，禁止插入(2,1)这类反向组合，要求不使用触发器，允许调整表结构。

解决方案 #

方案1：强制存储有序ID + 检查约束（MySQL 8.0.16+适用） #

核心逻辑是让location_id_1始终小于location_id_2，这样不管插入正向还是反向组合，最终存储的都是统一顺序的记录，再通过原有唯一约束阻止重复。

1. 给表添加检查约束：

ALTER TABLE `routes`
ADD CONSTRAINT chk_location_order CHECK (location_id_1 < location_id_2);

这个约束会直接拒绝任何location_id_1 >= location_id_2的插入/更新操作。

2. 插入数据时统一处理顺序：
   业务层插入数据时，用LEAST()和GREATEST()函数确保顺序正确：

-- 插入(2,1)会自动转为(1,2)
INSERT INTO routes (location_id_1, location_id_2)
VALUES (LEAST(2, 1), GREATEST(2, 1));

方案2：生成计算列 + 唯一约束（兼容低版本MySQL） #

如果你的MySQL版本低于8.0.16（不支持检查约束），可以用生成列来实现相同效果：

1. 添加两个存储型生成列：

ALTER TABLE `routes`
ADD COLUMN `smaller_loc` INT GENERATED ALWAYS AS (LEAST(location_id_1, location_id_2)) STORED,
ADD COLUMN `larger_loc` INT GENERATED ALWAYS AS (GREATEST(location_id_1, location_id_2)) STORED;

这两个列会自动根据location_id_1和location_id_2的值生成有序的ID对。

2. 给生成列添加唯一约束：

ALTER TABLE `routes`
ADD UNIQUE KEY uq_ordered_locations (smaller_loc, larger_loc);

不管插入的是(1,2)还是(2,1)，生成列的组合都是(1,2)，唯一约束会直接阻止重复插入。

方案3：重构表结构（无顺序依赖场景适用） #

如果业务逻辑不需要区分location_id_1和location_id_2的顺序（即(1,2)和(2,1)是同一个路由），可以直接重构表结构，去掉自增的route_id，用无序组合作为主键：

-- 删除原表（需提前备份数据）
DROP TABLE IF EXISTS `routes`;

-- 创建新的路由表
CREATE TABLE `routes`(
    `loc_a` INT NOT NULL,
    `loc_b` INT NOT NULL,
    FOREIGN KEY (`loc_a`) 
        REFERENCES `locations` (`location_id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY (`loc_b`) 
        REFERENCES `locations` (`location_id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    -- 直接约束无序组合唯一
    UNIQUE KEY uq_unordered_locs (LEAST(loc_a, loc_b), GREATEST(loc_a, loc_b)),
    PRIMARY KEY (`loc_a`, `loc_b`)
);

这种方式不需要额外处理插入顺序，数据库会自动识别反向组合为重复。

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