How to add foreign key that points to one of 2 tables with Postgres?

user_8450987 0
Viewed 138

I want to add a foreign key that is check record_id of activity exists in one of the record or personal_record tables. Is it possible to do with Postgres?

CREATE TABLE record
(
    id BIGSERIAL PRIMARY KEY
);

CREATE TABLE personal_record
(
    id BIGSERIAL PRIMARY KEY
);

CREATE TABLE activity
(
    id        BIGSERIAL PRIMARY KEY,
    record_id BIGINT NOT NULL,
    CONSTRAINT record_fk FOREIGN KEY (record_id) REFERENCES record (id)
);
postgresql sql
1 Answers

As stated, this is not possible. One possibility is generated columns and nullable references:

CREATE TABLE activity (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    private_record_id BIGINT,
    private_personal_record_id BIGINT,
    record_id BIGINT GENERATED ALWAYS AS (COLAESCE(private_record_id, private_personal_record_id)),
    CHECK( (private_record_id IS NOT NULL AND private_personal_record_id IS NULL) OR
           (private_record_id IS NULL AND private_personal_record_id IS NOT NULL)
         ),           
    CONSTRAINT fk_activity_record_id FOREIGN KEY (private_record_id) REFERENCES record(id),
    CONSTRAINT fk_activity_record_id FOREIGN KEY (private_personal_record_id) REFERENCES personal_record(id),
);

You might also want to look into inheritance.

user_11440350
Related