如何在MySQL中创建仅当main=true时的customer_id条件唯一约束？

你可以通过**筛选唯一索引（Partial Unique Index）**来实现这个需求，MySQL 5.7及以上版本支持该特性，正好匹配你的场景：仅当main = true时，强制customer_id与main的组合唯一。

1. 为现有表创建索引 #

如果表已经存在，执行以下SQL创建筛选唯一索引：

CREATE UNIQUE INDEX idx_customer_main ON customer_shop (customer_id, main)
WHERE main = true;

2. 创建表时直接定义约束 #

如果是新建表，可以把索引定义直接写在表结构中：

CREATE TABLE IF NOT EXISTS `customer_shop` (
      `customer_id` BINARY(16) NOT NULL,
      `shop_id` BINARY(16) NOT NULL,
      `main` BOOLEAN NOT NULL DEFAULT false,
      -- 定义筛选唯一索引
      UNIQUE INDEX idx_customer_main (customer_id, main) WHERE main = true
) ENGINE=InnoDB; -- 确保使用InnoDB引擎（MySQL默认）

索引生效逻辑 #

这个索引仅对main = true的记录生效：

• 同一个customer_id最多只能插入一条main=true的记录，重复插入会触发唯一约束错误；
• main=false的记录不受约束，同一个客户可以添加任意多条非主店铺记录。

测试验证 #

• 允许的操作：

-- 插入客户A的主店铺
INSERT INTO customer_shop (customer_id, shop_id, main) VALUES (UUID_TO_BIN(UUID()), UUID_TO_BIN(UUID()), true);
-- 插入客户A的多条非主店铺
INSERT INTO customer_shop (customer_id, shop_id, main) VALUES (@customer_a_id, UUID_TO_BIN(UUID()), false);
INSERT INTO customer_shop (customer_id, shop_id, main) VALUES (@customer_a_id, UUID_TO_BIN(UUID()), false);

• 禁止的操作（会报错）：

-- 尝试为客户A插入第二条主店铺
INSERT INTO customer_shop (customer_id, shop_id, main) VALUES (@customer_a_id, UUID_TO_BIN(UUID()), true);

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