MySQL约束实现：col2值仅归属同一col1的可行性问询

当然可以实现这个需求！不过得换个思路——普通的唯一键、外键这类原生约束没法直接搞定这种col2值只能属于某一个col1，同一col1下可重复使用，但跨col1绝对不能复用的逻辑。我给你两种靠谱的实现方案：

触发器是最直接的方式，不需要额外的表结构，插入或更新数据时自动检查约束逻辑。

首先先修正你原始表的SQL（MySQL里自增关键字是AUTO_INCREMENT，不是AUTOINCREMENT）：

CREATE TABLE constraint_table (
    id int PRIMARY KEY AUTO_INCREMENT,
    col1 int NOT NULL,
    col2 int NOT NULL
);

然后创建插入前检查触发器，确保新插入的col2没有被其他col1占用：

DELIMITER //
CREATE TRIGGER check_col2_exclusivity_insert
BEFORE INSERT ON constraint_table
FOR EACH ROW
BEGIN
    DECLARE existing_col1 INT;
    -- 查找当前col2已经绑定的col1
    SELECT col1 INTO existing_col1
    FROM constraint_table
    WHERE col2 = NEW.col2
    LIMIT 1;
    
    -- 如果找到的col1和新数据的col1不一致，抛出错误阻止插入
    IF existing_col1 IS NOT NULL AND existing_col1 != NEW.col1 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'col2值已被其他col1占用，无法复用';
    END IF;
END //
DELIMITER ;

再创建更新前检查触发器，防止修改数据时把col2改成已被其他col1占用的值：

DELIMITER //
CREATE TRIGGER check_col2_exclusivity_update
BEFORE UPDATE ON constraint_table
FOR EACH ROW
BEGIN
    DECLARE existing_col1 INT;
    -- 查找新col2绑定的col1（排除当前行本身，避免同一行修改col1但col2不变的误判）
    SELECT col1 INTO existing_col1
    FROM constraint_table
    WHERE col2 = NEW.col2 AND id != NEW.id
    LIMIT 1;
    
    IF existing_col1 IS NOT NULL AND existing_col1 != NEW.col1 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'col2值已被其他col1占用，无法复用';
    END IF;
END //
DELIMITER ;

这个方案的优点是操作和原来一样，直接插入/更新constraint_table就行；缺点是批量操作时性能会受影响，而且如果触发器被意外禁用，约束就失效了。

如果你更倾向于用MySQL原生约束来保证数据一致性，推荐用辅助表的方式，这种方案更稳定，性能也更好。

1. 先创建一个辅助表，专门记录col2和col1的绑定关系（每个col2只能对应一个col1，用col2做主键保证唯一性）：

CREATE TABLE col2_col1_mapping (
    col2 int PRIMARY KEY,
    col1 int NOT NULL
);

2. 修改原始表，添加外键约束，让constraint_table的(col2, col1)必须匹配辅助表中的记录：

ALTER TABLE constraint_table
ADD CONSTRAINT fk_col2_col1
FOREIGN KEY (col2, col1) REFERENCES col2_col1_mapping (col2, col1);

3. 插入数据时，需要先在辅助表中注册col2和col1的绑定关系（用INSERT IGNORE避免重复插入已绑定的记录）：

-- 先注册col2=1和col1=1的绑定
INSERT IGNORE INTO col2_col1_mapping (col2, col1) VALUES (1,1);
-- 再插入原始数据
INSERT INTO constraint_table (col1, col2) VALUES (1,1), (1,2), (1,1);

-- 注册col2=3、4和col1=2的绑定
INSERT IGNORE INTO col2_col1_mapping (col2, col1) VALUES (3,2), (4,2);
INSERT INTO constraint_table (col1, col2) VALUES (2,3), (2,4), (2,4);

当你试图插入非法数据（比如INSERT INTO constraint_table (col1, col2) VALUES (2,1)）时，外键约束会直接阻止操作，因为辅助表中col2=1对应的col1是1，和新数据的col1=2不匹配。

这个方案的优点是用原生约束保证数据一致性，不会因为触发器禁用失效；缺点是插入数据时需要先维护辅助表，多一步操作。

• 合法数据插入都会成功，完全符合你给出的示例；
• 非法数据（比如col1=2使用col2=1）会被触发器或外键约束阻止，抛出错误。

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