PostgreSQL中如何创建仅关联两张表中其一的外键约束

PostgreSQL本身不支持单个外键同时指向两张不同的表，你可以根据业务场景选择以下几种方案实现需求：

方案1：双可空外键+类型标记约束（最推荐） #

新增订单来源类型字段和两个分别指向tableA、tableB的可空外键，通过CHECK约束保证任意时刻只有一个外键有值，且和来源类型匹配。

-- 先创建枚举类型标记订单来源（也可以用字符串/数字代替）
CREATE TYPE order_source AS ENUM ('tableA', 'tableB');

CREATE TABLE tableC(
    order_source order_source NOT NULL,
    tablea_order_id INT,
    tableb_order_id INT,
    clientName varchar(50),
    clientAge integer,
    -- 两个外键分别关联对应表
    CONSTRAINT fk_tablea_order FOREIGN KEY(tablea_order_id) REFERENCES tableA(orderId),
    CONSTRAINT fk_tableb_order FOREIGN KEY(tableb_order_id) REFERENCES tableB(orderId),
    -- 校验约束：和来源匹配的字段必须有值，另一个必须为空
    CONSTRAINT chk_order_source_match CHECK (
        (order_source = 'tableA' AND tablea_order_id IS NOT NULL AND tableb_order_id IS NULL)
        OR
        (order_source = 'tableB' AND tableb_order_id IS NOT NULL AND tablea_order_id IS NULL)
    )
);

• 优点：约束逻辑明确，完全依托数据库原生外键能力保证一致性，排查问题简单
• 缺点：多了2个额外字段，如果后续新增其他订单来源表需要新增字段和修改约束

方案2：使用触发器自定义校验逻辑 #

如果不想修改原有表结构、保留统一的orderId字段，可以通过触发器在数据写入/更新时校验orderId是否存在于tableA或tableB中。

-- 首先创建校验函数
CREATE OR REPLACE FUNCTION check_order_exists() RETURNS TRIGGER AS $$
BEGIN
    IF EXISTS (SELECT 1 FROM tableA WHERE orderId = NEW.orderId) 
        OR EXISTS (SELECT 1 FROM tableB WHERE orderId = NEW.orderId) THEN
        RETURN NEW;
    ELSE
        RAISE EXCEPTION 'orderId % 不存在于tableA或tableB中', NEW.orderId;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 给tableC绑定触发器
CREATE TRIGGER trg_check_order_exists
BEFORE INSERT OR UPDATE OF orderId ON tableC
FOR EACH ROW EXECUTE FUNCTION check_order_exists();

-- 原有建表语句去掉外键约束即可
CREATE TABLE tableC(
    orderId INT NOT NULL,
    clientName varchar(50),
    clientAge integer
);

• 优点：不需要修改原有表结构，后续新增订单来源表只需要修改触发器函数即可
• 缺点：触发器逻辑不透明，高并发场景下性能比原生外键差，且不支持级联删除/更新等外键特性

方案3：表结构重构（适合订单逻辑统一的场景） #

如果tableA和tableB本质是两种不同类型的订单，可以抽象出公共的订单父表/分区表，将外键指向公共表即可：

-- 创建公共订单表
CREATE TABLE orders (
    orderId INT PRIMARY KEY,
    order_type VARCHAR(20) NOT NULL,
    -- 其他两个订单表的公共字段
    CHECK (order_type IN ('A', 'B'))
);

-- tableA和tableB改为继承orders，或者作为分区表挂载
CREATE TABLE tableA (
    -- tableA独有的字段
) INHERITS (orders);
CREATE TABLE tableB (
    -- tableB独有的字段
) INHERITS (orders);

-- 额外给orders表加触发器保证orderId全局唯一后，外键直接指向orders即可
CREATE TABLE tableC(
    orderId INT,
    clientName varchar(50),
    clientAge integer,
    CONSTRAINT fk_const FOREIGN KEY(orderId) REFERENCES orders(orderId)
);

• 优点：表结构符合业务建模逻辑，后续新增订单类型不需要修改tableC结构
• 缺点：需要改造现有两张订单表结构，改动成本较高

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