如何通过UNIQUE与CHECK约束实现单账户仅一个主地址？

嘿，这个场景我之前做项目时刚好遇到过，咱们一步步来拆解你的问题：

核心问题：能否用UNIQUE+CHECK实现需求？ #

直接给你答案：不能用你尝试的这种UNIQUE嵌套CHECK的方式实现，但有更合适的方法达成“单账户仅一个主地址”的需求，而且完美匹配你的测试用例！

为什么你的SQL会报错？ #

你写的语句语法完全不符合MySQL的规则——UNIQUE约束里不能直接嵌套CHECK子句。MySQL的UNIQUE只能指定列或者合法的表达式，你把CHECK条件直接塞进去，自然会触发1064语法错误。

正确的实现方案 #

方案1：MySQL 8.0.13+版本（推荐） #

从MySQL 8.0.13开始支持部分唯一索引，可以直接对is_principal = 1的记录强制account_id唯一：

ALTER TABLE address 
ADD CONSTRAINT unique_principal_address 
UNIQUE KEY (account_id) 
WHERE (is_principal = 1);

这个索引的作用是：同一个account_id下，最多只能存在一条is_principal = 1的记录；而is_principal = 0的非主地址可以随意添加，完全符合你的需求。

方案2：兼容低版本MySQL（8.0.13以下） #

如果你的MySQL版本不支持部分索引，可以用虚拟列+唯一索引的方式模拟：

1. 先添加一个存储型虚拟列：

ALTER TABLE address 
ADD COLUMN principal_account_id INT AS (CASE WHEN is_principal = 1 THEN account_id ELSE NULL END) STORED;

2. 给这个虚拟列添加唯一索引：

ALTER TABLE address 
ADD CONSTRAINT unique_principal_address 
UNIQUE KEY (principal_account_id);

原理是：MySQL的唯一索引允许多个NULL值，所以非主地址（is_principal = 0）对应的principal_account_id为NULL，不会触发约束；只有主地址（is_principal = 1）时，principal_account_id等于account_id，这时候会强制同一个account_id只能出现一次。

验证你的测试用例 #

• 可以成功执行的插入语句：

  INSERT INTO `address` (`address`, `account_id`, `is_principal`) VALUES ('address 1', 1, 1);
  INSERT INTO `address` (`address`, `account_id`, `is_principal`) VALUES ('address 2', 1, 0);
  INSERT INTO `address` (`address`, `account_id`, `is_principal`) VALUES ('address 3', 1, 0);
  

  这些语句都会执行成功，因为只有第一条是主地址，后两条是非主地址，不会触发唯一约束。

• 应该执行失败的插入语句：

  INSERT INTO `address` (`address`, `account_id`, `is_principal`) VALUES ('address 1', 1, 1);
  INSERT INTO `address` (`address`, `account_id`, `is_principal`) VALUES ('address 2', 1, 1);
  

  第二条插入会直接报错，因为同一个account_id下已经存在一条主地址记录，触发了唯一约束。

为什么单独用CHECK约束不行？ #

虽然MySQL从8.0.16开始正式支持CHECK约束（之前版本会忽略该约束），但CHECK只能校验单条记录的字段规则，没办法跨记录判断同一个account_id下的主地址数量，所以单独用CHECK或者和UNIQUE组合都没法实现这个需求。

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