如何基于列值设置数据库约束？请求表条件唯一约束实现问询

嘿，我明白你的需求了——要把requests表既用作活跃待审批请求的存储，又能保留所有历史请求记录，同时确保同一个用户不能向另一个用户重复发起未处理的请求对吧？你之前尝试的直接在UNIQUE里加条件的写法确实行不通，因为MySQL的普通唯一约束不支持这种条件判断，但我们有两种靠谱的方案，看你使用的MySQL版本来选择：

方案一：部分唯一索引（推荐，MySQL 8.0.13+支持） #

从MySQL 8.0.13开始，官方支持了部分唯一索引（Partial Unique Index），可以直接在创建索引时指定生效条件，完美匹配你的需求：

CREATE TABLE IF NOT EXISTS requests (
  requested_at timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
  -- 注意：把待审批状态的默认值改成零值，而非CURRENT_TIMESTAMP
  approved_at timestamp DEFAULT '1970-01-01 00:00:00' NOT NULL,
  denied_at timestamp DEFAULT '1970-01-01 00:00:00' NOT NULL,
  user1_id int NOT NULL,
  user2_id int NOT NULL,
  -- 区分两个外键的名字，避免冲突
  CONSTRAINT fk_requests_user1 FOREIGN KEY (user1_id) REFERENCES user(id),
  CONSTRAINT fk_requests_user2 FOREIGN KEY (user2_id) REFERENCES user(id),
  -- 仅当请求处于待审批状态时，user1_id和user2_id的组合唯一
  UNIQUE KEY unique_pending_request (user1_id, user2_id)
  WHERE (approved_at = '1970-01-01 00:00:00' AND denied_at = '1970-01-01 00:00:00')
);

原理说明 #

这个唯一索引只会在approved_at和denied_at都为零值（即请求未被审批或拒绝）时生效，阻止同一user1_id向同一user2_id重复发起请求。一旦你更新了approved_at或denied_at为非零时间（标记请求已处理），这条记录就会脱离该约束的限制，你可以正常插入同用户组合的新请求了。

方案二：虚拟列+唯一约束（兼容MySQL 8.0.13以下版本） #

如果你的MySQL版本低于8.0.13，不支持部分索引，可以通过生成列（虚拟列）+ 唯一约束的方式间接实现：

CREATE TABLE IF NOT EXISTS requests (
  requested_at timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
  approved_at timestamp DEFAULT '1970-01-01 00:00:00' NOT NULL,
  denied_at timestamp DEFAULT '1970-01-01 00:00:00' NOT NULL,
  user1_id int NOT NULL,
  user2_id int NOT NULL,
  -- 生成一个标记列：待审批时为'Y'，已处理时为NULL
  pending_marker VARCHAR(1) GENERATED ALWAYS AS (
    CASE 
      WHEN approved_at = '1970-01-01 00:00:00' AND denied_at = '1970-01-01 00:00:00' 
      THEN 'Y' 
      ELSE NULL 
    END
  ) STORED,
  CONSTRAINT fk_requests_user1 FOREIGN KEY (user1_id) REFERENCES user(id),
  CONSTRAINT fk_requests_user2 FOREIGN KEY (user2_id) REFERENCES user(id),
  -- 唯一约束包含标记列，利用MySQL对NULL的特殊处理实现条件唯一
  UNIQUE KEY unique_pending_request (user1_id, user2_id, pending_marker)
);

原理说明 #

• 生成列pending_marker会根据请求状态自动赋值：待审批时为固定值'Y'，已处理时为NULL。
• MySQL的唯一约束会忽略NULL值，因此当pending_marker为NULL时（已处理的历史请求），同一user1_id和user2_id的组合可以存在多条记录；而当pending_marker为'Y'时，(user1_id, user2_id, 'Y')的组合必须唯一，从而阻止重复的待审批请求。

额外注意事项 #

1. 修正默认值：你原来的表结构中approved_at和denied_at的默认值是CURRENT_TIMESTAMP，这不符合你“待审批时为零值”的需求，一定要改成'1970-01-01 00:00:00'。
2. 外键命名：两个外键的名字要区分开（比如fk_requests_user1和fk_requests_user2），否则会因同名约束报错。
3. 修改现有表：如果是要修改已存在的表，使用ALTER TABLE语句添加约束即可。比如添加部分唯一索引：

   ALTER TABLE requests
   ADD UNIQUE KEY unique_pending_request (user1_id, user2_id)
   WHERE (approved_at = '1970-01-01 00:00:00' AND denied_at = '1970-01-01 00:00:00');
   

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