如何为ident与label_one/label_two的联合创建唯一性约束？

直接用常规的UNIQUE联合约束无法实现需求——因为常规约束针对的是单行列值的组合，而你需要的是同一个ident下，所有label_one和label_two的值（无论来自哪一列）都不能重复。下面提供两种可行的实现方式：

方法一：函数式唯一索引（PostgreSQL专属） #

利用PostgreSQL对数组和函数式索引的支持，创建覆盖所有label值的唯一索引，自动拦截重复值：

-- 创建索引：过滤NULL值，确保同一个ident下的所有非空label唯一
CREATE UNIQUE INDEX idx_unique_labels_per_ident 
ON items (ident, unnest(array[label_one, label_two]))
WHERE unnest(array[label_one, label_two]) IS NOT NULL;

验证效果 #

• 可成功执行的插入操作：

INSERT INTO items (ident, label_one, label_two) VALUES ('foo', 'a', 'b');

• 以下插入操作均会触发唯一索引冲突，执行失败：
  1. 重复已存在的label_one值：

  INSERT INTO items (ident, label_one, label_two) VALUES ('foo', 'a', 'x');
  

  2. label_one使用已存在于label_two的值：

  INSERT INTO items (ident, label_one, label_two) VALUES ('foo', 'b', 'x');
  

  3. label_two使用已存在于label_one的值：

  INSERT INTO items (ident, label_one, label_two) VALUES ('foo', 'x', 'a');
  

  4. 重复已存在的label_two值：

  INSERT INTO items (ident, label_one, label_two) VALUES ('foo', 'x', 'b');
  

方法二：辅助表+触发器（通用SQL方案） #

如果你的数据库不支持函数式索引（如MySQL），可通过拆分数据到辅助表的方式实现约束：

1. 创建辅助表存储每个ident对应的所有label：

CREATE TABLE item_labels (
  ident text NOT NULL,
  label text NOT NULL,
  -- 关联主表，删除主表行时同步删除辅助表数据
  FOREIGN KEY (ident) REFERENCES items(ident) ON DELETE CASCADE,
  -- 核心约束：同一个ident下label唯一
  PRIMARY KEY (ident, label)
);

2. 创建触发器，在主表插入/更新时同步维护辅助表：

-- 插入/更新同步函数
CREATE OR REPLACE FUNCTION sync_item_labels()
RETURNS TRIGGER AS $$
BEGIN
  -- 更新时先删除旧的label记录
  DELETE FROM item_labels WHERE ident = NEW.ident;
  -- 插入label_one
  INSERT INTO item_labels (ident, label) VALUES (NEW.ident, NEW.label_one);
  -- 若label_two非空，插入label_two
  IF NEW.label_two IS NOT NULL THEN
    INSERT INTO item_labels (ident, label) VALUES (NEW.ident, NEW.label_two);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到主表的插入和更新操作
CREATE TRIGGER trigger_sync_item_labels
AFTER INSERT OR UPDATE ON items
FOR EACH ROW EXECUTE FUNCTION sync_item_labels();

验证效果 #

与方法一完全一致：主表插入符合要求的数据成功，插入冲突数据时，辅助表的主键约束会触发错误，导致主表操作失败。

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