PostgreSQL 15能否将子表列关联到父表两列的拼接值？

直接通过你尝试的REFERENCES ... || ...语法定义外键是不可行的——PostgreSQL的外键约束要求引用的是表中实际存在的列（或生成列），或者目标表的唯一约束列集合，不支持直接在REFERENCES子句中使用拼接表达式。

以下是两种无需拆分子表id列的可行方案：

方案1：父表添加生成列+唯一约束（推荐） #

通过给父表添加一个自动生成的拼接列，再基于该列创建唯一约束，即可让子表的id列正常关联外键：

1. 给父表添加存储式生成列，自动拼接categoryID和productID：

ALTER TABLE public."parent table"
ADD COLUMN category_product_id TEXT GENERATED ALWAYS AS (categoryID || productID) STORED;

2. 为生成列添加唯一约束（外键要求引用的列必须具备唯一性）：

ALTER TABLE public."parent table"
ADD CONSTRAINT uq_parent_category_product UNIQUE (category_product_id);

3. 给子表添加外键约束，关联父表的生成列：

-- 替换为你的子表实际名称
ALTER TABLE public.child_table
ADD CONSTRAINT fk_child_parent_id FOREIGN KEY (id) REFERENCES public."parent table" (category_product_id);

方案优势 #

• 完全符合PostgreSQL的约束机制，数据一致性由数据库原生保障
• 性能优于触发器方案，查询和约束检查效率更高
• 维护成本低，生成列会自动随父表的categoryID/productID更新

方案2：使用触发器验证（无需修改父表） #

如果无法修改父表结构，可以通过触发器在子表插入/更新时验证id是否匹配父表的拼接值：

1. 创建验证函数：

CREATE OR REPLACE FUNCTION validate_child_parent_id()
RETURNS TRIGGER AS $$
BEGIN
  -- 检查新id是否存在于父表的拼接组合中
  IF NOT EXISTS (
    SELECT 1 FROM public."parent table"
    WHERE categoryID || productID = NEW.id
  ) THEN
    RAISE EXCEPTION '无效id：% 未匹配父表的categoryID+productID组合', NEW.id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 给子表绑定触发器，在插入/更新前执行验证：

-- 替换为你的子表实际名称
CREATE TRIGGER trigger_validate_child_id
BEFORE INSERT OR UPDATE ON public.child_table
FOR EACH ROW EXECUTE FUNCTION validate_child_parent_id();

方案注意事项 #

• 触发器仅在子表数据变更时生效，若父表的categoryID/productID被修改，不会自动检查子表的关联数据（需额外添加父表的触发器来处理）
• 性能略低于生成列方案，每次数据变更都要执行查询验证

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