Non-unique multicolumn foreign key

All we need is an easy explanation of the problem, so here it is.

I have a “comments” table that models a conversation on a topic, like this:

id serial  
topic_id integer  
parent_comment_id integer
body text

So, every comment has a reference to its topic AND eventually its parent comment (if it’s not the first comment on the topic).
I’d like to add a constraint that would prevent adding rows having mismatched topic/parent (for example by referencing a topic that doesn’t have the required comment, or conversely a comment that references the wrong topic).
Is this possible? Is a trigger required?

(For the record, I tried

ALTER TABLE comments ADD FOREIGN KEY (parent_comment_id, topic_id)
                 REFERENCES comments (id, topic_id)

but it complains that there is no unique constraint matching given keys for referenced table "comments")

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You need to add super-key (unique index/constraint) on both (id,topic_id). This gives you the “target” uniqueness to create your foreign key. This acts like a CHECK constraint in this case.

ALTER TABLE comments ADD 
    FOREIGN KEY (parent_comment_id, topic_id) REFERENCES comments (id, topic_id)

Note: id remains as primary key to preserve the model. Even if id is serial, it’s would be wrong from a modelling perspective to change the PK to (id,topic_id)

Method 2

Try

ALTER TABLE comments ADD FOREIGN KEY (parent_comment_id, topic_id)
    REFERENCES comments (id)

If you want this to work:

ALTER TABLE comments ADD FOREIGN KEY (parent_comment_id, topic_id)
    REFERENCES comments (id, topic_id)

Then I believe you need to change your PK to be on both the id and topic_id columns.

Also, I think this link helps explain what you need: http://www.postgresql.org/docs/8.1/static/ddl-constraints.html

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply