Is there a way to have a column reference one of two columns on seperate tables?

user_15709786 0
Viewed 47

I am creating a system that has Posts and Comments. The Posts reference to a Location as seen below.

CREATE SEQUENCE IF NOT EXISTS reference_id;
   ...

CREATE TABLE Posts 
(
    id BIGINT DEFAULT nextval('reference_id') PRIMARY KEY,
    username INTEGER
        REFERENCES Users,
    location_id INTEGER
        REFERENCES Locations,
    created_at timestamp DEFAULT current_timestamp,
    subject TEXT,
    body text NOT NULL
);

CREATE TABLE Comments 
(
    id BIGINT DEFAULT nextval('reference_id') PRIMARY KEY,
    username INTEGER
        REFERENCES Users,
    reference_id INTEGER
        REFERENCES Posts,
    created_at timestamp DEFAULT current_timestamp,
    body text NOT NULL
);

I have created a sequence so that both Posts and Comments can share a set of ids, so they are all unique. But now, I need the Comments to reference either a Post OR another Comment. Like

reference_id INTEGER
        REFERENCES Posts OR Comments,

I'm thinking I might have to create a third table, but even that seems... obscure to me. Every time I think about it, it seems I'm stuck in some sort of recursive loop of "well, this comment is a child level which will link to posts, and then I need a grandchild level comment that references just child level comments, and then I need a great-grandchild level comment..."

Is there a way to do this? Preferably without a third table, but whatever works, I'll take.

postgresql sql
1 Answers

There is no support for an OR reference.

Have two nullable columns, one for each FK:

CREATE TABLE Comments (
  ...
  posts_reference_id INTEGER REFERENCES Posts,
  comments_reference_id INTEGER REFERENCES Comments,
  ...
)

Or since you seem to want comments and posts to be an interchangeable FK, delete the comments table and introduce a post_type column that can be either 'post' or 'comment'

user_2561960
Related