9

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

Improve this question
4
  • does a primary key exist on the 'comments' table?
    – SQLRockstar
    Commented Mar 15, 2011 at 21:37
  • Yes sorry, it's the id
    – Joril
    Commented Mar 15, 2011 at 21:51
  • 1
    Try create unique index how_you_want_to_name_this_index on comments (id, topic_id); before issuing the alter table command adding the foreign key.
    – j.p.
    Commented Mar 16, 2011 at 8:48
  • Or just add the line unique (id, topic_id), in your "create table"-command for comments.
    – j.p.
    Commented Mar 16, 2011 at 8:50

2 Answers 2

Reset to default
5

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)

Improve this answer
2
  • 1
    That is technically correct, but logically this is no new constraint. Given the primary key on id, it is always fulfilled. I.e. it is just a disabilities of the implementation of the DBMS.
    – bernd_k
    Commented Mar 16, 2011 at 11:27
  • @bernd_k: this is why I said "super key". It also decouples actual PK from the superkey/FK.
    – gbn
    Commented Mar 16, 2011 at 11:45
1

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

Improve this answer
2
  • 1
    The first command gives me number of referencing and referenced columns for foreign key disagree :/ I think the problem with my command is that there could be multiple rows with matching parent + topic
    – Joril
    Commented Mar 15, 2011 at 22:35
  • OK, can you alter the primary key to have two columns? then your original command should work, i believe.
    – SQLRockstar
    Commented Mar 15, 2011 at 22:57

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.