如何在PSQL中实现表的email与alt_email两列跨列全局唯一约束

方案1：使用排他约束（推荐，PG 12+支持） #

不需要编写额外逻辑，由数据库原生约束保证可靠性，操作步骤如下：

• 首先安装btree_gist扩展，排他约束需要该扩展支持普通数据类型的GiST索引：

CREATE EXTENSION IF NOT EXISTS btree_gist;

• 给目标表添加排他约束，将两个邮箱字段合并为数组后展开，约束所有展开后的邮箱值全局唯一：

ALTER TABLE 你的表名
ADD CONSTRAINT no_duplicate_emails
EXCLUDE USING gist (
  unnest(ARRAY[email, alt_email]) WITH =
);

方案2：使用触发器（兼容所有PG版本） #

如果你的PostgreSQL版本低于12，可以用触发器实现校验逻辑：

• 首先创建校验函数：

CREATE OR REPLACE FUNCTION check_email_duplicate()
RETURNS TRIGGER AS $$
BEGIN
  -- 校验新主邮箱是否已存在
  IF EXISTS (
    SELECT 1 FROM 你的表名
    WHERE (email = NEW.email OR alt_email = NEW.email)
    AND id != NEW.id -- 排除当前记录自身，适配更新场景
  ) THEN
    RAISE EXCEPTION '邮箱 % 已存在，不允许重复录入', NEW.email;
  END IF;

  -- 校验新备用邮箱是否已存在
  IF EXISTS (
    SELECT 1 FROM 你的表名
    WHERE (email = NEW.alt_email OR alt_email = NEW.alt_email)
    AND id != NEW.id
  ) THEN
    RAISE EXCEPTION '邮箱 % 已存在，不允许重复录入', NEW.alt_email;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

• 给目标表绑定触发器，仅在插入或修改两个邮箱字段时触发校验：

CREATE TRIGGER trigger_check_email_duplicate
BEFORE INSERT OR UPDATE OF email, alt_email
ON 你的表名
FOR EACH ROW EXECUTE FUNCTION check_email_duplicate();

注意事项 #

无论使用哪种方案，添加约束前请先清理表中已有的重复邮箱数据，否则约束/触发器会添加失败。

如果后续有扩展更多邮箱类型的需求，建议把邮箱字段拆分为独立关联表，给邮箱字段加普通唯一索引即可实现需求，扩展性更强：

-- 示例：用户邮箱关联表结构
CREATE TABLE user_emails (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES 你的原表名(id) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  is_primary BOOLEAN NOT NULL DEFAULT FALSE
);

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