databasepostgresqlforeign-keysrdbmsobject-oriented-database

How to reference the primary key at the derived part of the same table?


Let's consider the following two, simplified tables:

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);

Now, how can I create a foreign key reference of subscription_payments.parent_id to payments.id in which it wouldn't reference other inheriting tables?

In other words, how can I reference subscription_payments.id?


Solution

  • As this is a bridge you can simply reference payments, this would then guarantee that a payment was made.

    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
    )