0

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?

Improve this question
1

1 Answer 1

Reset to default
0

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:

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

  2. 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

  3. 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.

Useful References

Improve this answer
4
  • Thank you for the answer. But could you please give me a code example as to how to achieve item 1 in your suggestions? It's the only one I don't think is that trivial.
    – psygo
    Commented Jul 28, 2023 at 14:10
  • Also, one other thing since you seem far more knowledgeable on SQL than I am: do you have recommendations of books or other references for these common SQL design problems? My knowledge on SQL is probably 100% web-search-based, but I feel like so many problems I have should be in a good SQL design book somewhere...
    – psygo
    Commented Jul 28, 2023 at 14:12
  • 1
    I have no reference to literature; this is just what I think and what I picked up along the way.
    – Laurenz Albe
    Commented Jul 28, 2023 at 16:20
  • Ok, I'll accept the answer, but could you still add code to item 1?
    – psygo
    Commented Jul 29, 2023 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.