PostgreSQL中如何为非唯一列实现外键级别的存在性校验

你遇到的报错是因为外键约束要求被引用的字段必须在被引用表中有唯一约束（主键/唯一键），而你的items表中item_code可重复，不满足这个要求，所以无法直接对item_code建外键。

方案1：新增唯一item_code主表（推荐） #

这是符合数据库设计范式的最优方案，不会破坏原有表结构，也能完整实现外键的校验、级联等能力：

-- 1. 新建商品编码主表，存储所有全局唯一的item_code
CREATE TABLE item_master (
    item_code VARCHAR PRIMARY KEY
);

-- 2. 导入现有items表中所有去重的item_code
INSERT INTO item_master (item_code)
SELECT DISTINCT item_code FROM items;

-- 3. 给原有items表添加外键，保证items表新增的item_code都合法
ALTER TABLE items
ADD CONSTRAINT fk_items_item_code FOREIGN KEY (item_code) REFERENCES item_master(item_code);

-- 4. 需要做item_code校验的业务表，直接引用item_master的主键即可
ALTER TABLE your_business_table
ADD CONSTRAINT fk_biz_item_code FOREIGN KEY (item_code) REFERENCES item_master(item_code);

后续新增商品编码时，先插入item_master表再操作items表即可，全程由数据库保证数据合法性。

方案2：使用触发器做行级校验（无需新增表） #

如果你不想调整现有表结构，可以通过触发器在写入时主动校验item_code的存在性：

-- 1. 新建校验函数
CREATE OR REPLACE FUNCTION check_item_code_exists()
RETURNS TRIGGER AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM items WHERE item_code = NEW.item_code) THEN
        RAISE EXCEPTION 'item_code % 不存在于items表中', NEW.item_code;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2. 给需要校验的业务表绑定触发器，插入、更新item_code时触发校验
CREATE TRIGGER trigger_check_item_code
BEFORE INSERT OR UPDATE OF item_code ON your_business_table
FOR EACH ROW EXECUTE FUNCTION check_item_code_exists();

注意：该方案仅校验业务表的写入动作，若后续items表中对应item_code被全部删除，业务表中已写入的历史数据不会自动校验和处理，和原生外键的级联逻辑有差异。如果需要级联清理/校验，可在items表上补充对应触发器实现。

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