Non-unique multicolumn foreign key

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")

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)

StackExchange Database Administrators Q#1761, answer score: 5