PostgreSQL如何设置列约束允许NULL或users表已存在的邮箱值

问题背景 #

编写自定义函数校验邮箱是否存在于users表，逻辑为仅当邮箱存在时才允许写入其他表对应行记录。当前需要实现规则：对应邮箱字段既允许填入users表中已存在的合法邮箱，也允许为NULL，现有约束无法满足NULL值放行要求。
由于业务关联逻辑特殊，此处仅以user/account关系做场景举例，无法直接使用常规表关联逻辑实现。

初始实现与现存问题 #

初始自定义校验函数 #

CREATE OR REPLACE FUNCTION check_email_allowed(text)
  RETURNS bool AS
$func$
SELECT EXISTS (SELECT 1 FROM users WHERE email = $1);
$func$
  LANGUAGE sql STABLE;  -- not actually IMMUTABLE

初始CHECK约束 #

ALTER TABLE accounts ADD CONSTRAINT email_allowed
CHECK (email_allowed(email)) NOT VALID;

该约束可正常校验已存在的有效邮箱，但传入NULL值时会抛出错误，无法放行NULL值。

尝试过的外键方案 #

ALTER TABLE accounts ADD CONSTRAINT email_allowed 
FOREIGN KEY (email) REFERENCES users(email);

测试发现外键方案同样无法正常写入NULL值。

涉及表结构示例 #

• accounts表

• users表

可行实现方案 #

方案1：修改CHECK约束逻辑（适配无法使用外键的特殊业务场景） #

原有约束报错的核心原因是：当传入NULL值时，函数内WHERE email = NULL的判断结果为未知，EXISTS子句返回false，直接触发CHECK约束拦截写入。
只需要调整约束判断规则，显式对NULL值放行即可，不需要修改原有校验函数：

-- 删除旧的错误约束
ALTER TABLE accounts DROP CONSTRAINT IF EXISTS email_allowed;
-- 新增调整后的约束
ALTER TABLE accounts ADD CONSTRAINT email_allowed
CHECK (email IS NULL OR check_email_allowed(email)) NOT VALID;

约束逻辑说明：

• 当字段值为NULL时，email IS NULL返回true，整个OR表达式直接判定为校验通过，不会执行后续的邮箱存在性校验
• 当字段值非NULL时，才会调用函数校验邮箱是否在users表中存在，不存在则拦截写入

方案2：使用外键约束（无特殊业务限制时优先选择） #

标准PostgreSQL的外键约束默认就允许外键列写入NULL值，如果测试时外键写入NULL报错，优先排查以下问题：

• 对应存储邮箱的字段是否被设置了NOT NULL非空约束
• 表上是否存在其他触发器、CHECK约束拦截NULL值写入
  排查完上述问题后，原生外键方案性能更好，还能自动保证关联数据一致性，不需要额外维护自定义函数，是更优的通用选择。

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