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