Foreign Key Referencing ID of One of 2+ Other Tables

Question

Given 3 tables — e.g. posts, comments, and tags — is there a way of having a foreign key in one of them referencing a primary key of one of the other two?

CREATE TABLE IF NOT EXISTS posts (
    id UUID DEFAULT uuid_generate_v4 ()
        CONSTRAINT posts_pk PRIMARY KEY
);
CREATE TABLE IF NOT EXISTS comments (
    id UUID DEFAULT uuid_generate_v4 ()
        CONSTRAINT comments_pk PRIMARY KEY
);
CREATE TABLE IF NOT EXISTS tags (
    id UUID DEFAULT uuid_generate_v4 ()
        CONSTRAINT tags_pk PRIMARY KEY
);
DROP TYPE IF EXISTS s_type;
CREATE TYPE s_type AS ENUM (
    'POST',
    'COMMENT'
);
CREATE TABLE IF NOT EXISTS tag_mentions (
    id UUID DEFAULT uuid_generate_v4 ()
        CONSTRAINT tag_mentions_pk PRIMARY KEY,
    source_type s_type NOT NULL,
    source_id UUID NOT NULL
        CONSTRAINT tag_mention_id_fk
            -- Is something like this possible?
            REFERENCES posts (id) OR comments (id)
);

Maybe there's a way of doing this with a composite index between s_type and posts and comments IDs?

Laurenz Albe · Accepted Answer · 2023-07-30T18:49:13.860

This is a notoriously hard problem in SQL, and there is no entirely satisfying solution. You will find many similar questions such as this one.

The best, or least bad, solution depends on the concrete use case. Here are some of the classical approaches:

create two foreign key column in tag_mentions and use a check constraint that makes sure that only one of them is NOT NULL and that it matches the source_type setting

ALTER TABLE tag_mentions
   ADD posts_id uuid REFERENCES posts,
   ADD comments_id uuid REFERENCES comments,
   ADD CHECK (
          (source_type = 'POST' AND
           posts_id IS NOT NULL AND
           comments_id IS NULL)::integer +
          (source_type = 'COMMENT' AND
           posts_id IS NULL AND
           comments_id IS NOT NULL)::integer
          = 1
       );

instead of two tables, create a single table posts_comments that contains the columns from both entities and a type column, then reference this table with the foreign key
have the foreign keys point in the other direction, from posts and comments to tag_mentions

The third solution has the disadvantage that you cannot prevent both a posts and comments entry from being related to a single tag_mentions.

The choice between 1. and 2. usually depends on your concrete situation: if posts and comments are very similar, 2. is appealing. Also, if there are not two categories (posts and comments), but way more, 2. is probably the better solution. Otherwise, 1. may be the way to go.

Foreign Key Referencing ID of One of 2+ Other Tables

1 Answers1

Useful References