PostgreSQL继承表外键报错：如何让actions关联子表数据？

问题原因 #

PostgreSQL的表继承机制中，子表（如animals、water）的记录不会自动写入父表targets，父表仅存储直接插入自身的记录。因此你创建的外键约束target_id references targets(id)只会校验父表中存在的ID，子表中的ID不在父表数据范围内，就会触发外键冲突错误。

方案1：触发器同步子表数据到父表 #

通过触发器将子表的插入、更新、删除操作同步到父表targets，让父表包含所有子表的ID，满足外键约束的校验要求。

首先创建同步用的触发器函数：

CREATE OR REPLACE FUNCTION sync_targets()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    -- 插入时同步到父表，ID冲突则忽略
    INSERT INTO targets(id, name) VALUES(NEW.id, NEW.name) ON CONFLICT(id) DO NOTHING;
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    -- 更新时同步父表的name字段
    UPDATE targets SET name = NEW.name WHERE id = NEW.id;
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    -- 删除时仅当所有子表都无该ID记录时，才删除父表对应行
    IF NOT EXISTS (SELECT 1 FROM animals WHERE id = OLD.id) 
       AND NOT EXISTS (SELECT 1 FROM water WHERE id = OLD.id) THEN
      DELETE FROM targets WHERE id = OLD.id;
    END IF;
    RETURN OLD;
  END IF;
END;
$$ LANGUAGE plpgsql;

为animals和water表绑定触发器：

-- 绑定animals表的同步触发器
CREATE TRIGGER trigger_animals_sync_targets
AFTER INSERT OR UPDATE OR DELETE ON animals
FOR EACH ROW EXECUTE FUNCTION sync_targets();

-- 绑定water表的同步触发器
CREATE TRIGGER trigger_water_sync_targets
AFTER INSERT OR UPDATE OR DELETE ON water
FOR EACH ROW EXECUTE FUNCTION sync_targets();

之后子表的记录会自动同步到父表，外键约束即可正常生效。

方案2：改用分区表（适合按规则拆分的场景） #

如果animals和water是targets按业务规则拆分的子集，可以将targets创建为分区表，子表作为分区存在。这种情况下，分区的记录会被父表"逻辑包含"，外键约束可直接识别分区中的ID。

首先重建targets为分区表（示例按target_type字段分区，你可根据实际业务调整分区规则）：

CREATE TABLE targets (
    id           integer NOT NULL,
    name         text,
    target_type  text NOT NULL, -- 新增字段用于区分分区类型
    PRIMARY KEY (id, target_type) -- 分区表主键需包含分区键
) PARTITION BY LIST (target_type);

创建animals和water分区，并添加各自的额外字段：

-- 创建animals分区
CREATE TABLE animals PARTITION OF targets FOR VALUES IN ('animal');
ALTER TABLE animals ADD COLUMN type_id integer;
ALTER TABLE animals ADD COLUMN flock_id integer;

-- 创建water分区
CREATE TABLE water PARTITION OF targets FOR VALUES IN ('water');
-- 添加water表的额外字段，示例：
-- ALTER TABLE water ADD COLUMN xxx integer;

插入到分区表的记录会自动被视为targets的一部分，外键约束target_id references targets(id)可直接校验分区中的ID。

方案3：多态关联（无需表继承） #

如果表继承不是必须的，可以通过给actions表添加target_type字段，配合触发器实现多态关联，手动校验target_id在对应子表中的存在性。

修改actions表结构：

CREATE TABLE actions (
    id            integer NOT NULL PRIMARY KEY,
    target_id     integer,
    target_type   text CHECK (target_type IN ('animal', 'water')), -- 限制可选类型
    UNIQUE(target_id, target_type) -- 唯一约束确保组合唯一
);

创建校验用的触发器函数：

CREATE OR REPLACE FUNCTION validate_target()
RETURNS TRIGGER AS $$
BEGIN
  CASE NEW.target_type
    WHEN 'animal' THEN
      IF NOT EXISTS (SELECT 1 FROM animals WHERE id = NEW.target_id) THEN
        RAISE EXCEPTION 'ID为%的动物记录不存在', NEW.target_id;
      END IF;
    WHEN 'water' THEN
      IF NOT EXISTS (SELECT 1 FROM water WHERE id = NEW.target_id) THEN
        RAISE EXCEPTION 'ID为%的水源记录不存在', NEW.target_id;
      END IF;
  END CASE;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

绑定触发器到actions表：

CREATE TRIGGER trigger_validate_target
BEFORE INSERT OR UPDATE ON actions
FOR EACH ROW EXECUTE FUNCTION validate_target();

这种方式无需依赖继承，直接明确关联的表类型，实现灵活的多态关联校验。

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