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