MySQL中如何在表a实现基于表b的x、y字段唯一性约束

根据你的需求，分两种场景给出对应实现方式：

场景1：禁止表a中存在关联同一b行的多条记录 #

如果你的真实需求是同一个b表行（即相同b__fk）不能在a表中对应多条记录，直接给a.b__fk添加唯一约束即可，这是最简单的方式：

ALTER TABLE a
ADD UNIQUE INDEX idx_unique_b_fk (b__fk);

场景2：禁止表a中存在关联b表x、y组合相同的任意行的多条记录 #

如果需要严格实现unique(b.x, b.y)的约束效果（即无论b表中是否存在多行x、y相同的记录，a表中都不能有对应这些x、y组合的多条记录），可以通过冗余字段+唯一约束+触发器来实现，具体步骤如下：

1. 修改表a，添加冗余字段和唯一约束 #

在a表中新增存储b表x、y值的冗余字段，并给这两个字段添加联合唯一约束：

ALTER TABLE a
ADD COLUMN b_x INT NOT NULL,
ADD COLUMN b_y INT NOT NULL,
ADD UNIQUE INDEX idx_unique_b_xy (b_x, b_y);

2. 初始化现有数据的冗余字段 #

将a表已存在记录对应的b表x、y值填充到新增字段中：

UPDATE a
JOIN b ON a.b__fk = b.pk
SET a.b_x = b.x, a.b_y = b.y;

3. 创建触发器维护数据一致性 #

为了保证a表的冗余字段与b表数据始终同步，需要创建三个触发器：

插入a表时自动填充b_x、b_y

DELIMITER //
CREATE TRIGGER trg_a_insert_fill_xy
BEFORE INSERT ON a
FOR EACH ROW
BEGIN
    SELECT x, y INTO NEW.b_x, NEW.b_y
    FROM b
    WHERE pk = NEW.b__fk;
END //
DELIMITER ;

更新a表的b__fk时同步更新b_x、b_y

DELIMITER //
CREATE TRIGGER trg_a_update_fill_xy
BEFORE UPDATE ON a
FOR EACH ROW
IF NEW.b__fk <> OLD.b__fk THEN
    SELECT x, y INTO NEW.b_x, NEW.b_y
    FROM b
    WHERE pk = NEW.b__fk;
END IF //
DELIMITER ;

b表的x或y更新时同步更新a表对应记录的冗余字段

DELIMITER //
CREATE TRIGGER trg_b_update_sync_a_xy
AFTER UPDATE ON b
FOR EACH ROW
IF NEW.x <> OLD.x OR NEW.y <> OLD.y THEN
    UPDATE a
    SET b_x = NEW.x, b_y = NEW.y
    WHERE b__fk = NEW.pk;
END IF //
DELIMITER ;

内容的提问来源于stack exchange，提问作者Eduardo Costa de Souza