Database 如何在同一个表的派生部分引用主键

发布于08月19日

让我们考虑以下两个简化表:

CREATE TABLE payments
(
    id bigserial NOT NULL PRIMARY KEY,
    method int,
    method_name name,
    total numeric,
    payment_date timestamp without time zone,
    is_seccessful boolean,
    data jsonb
);

CREATE TABLE subscription_payments
(
    subscription_id int,
    parent_id bigint,

   CONSTRAINT pk_subscription_id FOREIGN KEY (subscription_id)
       REFERENCES subscriptions (id)
       ON DELETE RESTRICT
)
INHERITS (payments);

现在，我如何创建一个从subscription_payments.parent_id到payments.id的外键引用，而不引用其他继承表呢？

换句话说，我怎么能引用subscription_payments.id呢？

推荐答案

由于这是一座桥，您可以简单地引用付款，这将确保付款.

CREATE TABLE subscription_payments
(
    subscription_id int,
    parent_id bigint,

    primary key (subscription_id, parent_id),
    CONSTRAINT fk_subscription_id FOREIGN KEY (subscription_id)
        REFERENCES subscriptions (id)
        ON DELETE RESTRICT,
    CONSTRAINT fk_parent_id  FOREIGN KEY (parent_id )
        REFERENCES payments(id)
        ON DELETE RESTRICT
)