Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
does a primary key exist on the 'comments' table? –  SQLRockstar Mar 15 '11 at 21:37
    
Yes sorry, it's the id –  Joril Mar 15 '11 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. Mar 16 '11 at 8:48
    
Or just add the line unique (id, topic_id), in your "create table"-command for comments. –  j.p. Mar 16 '11 at 8:50
add comment

2 Answers 2

up vote 3 down vote accepted

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)

share|improve this answer
    
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 Mar 16 '11 at 11:27
    
@bernd_k: this is why I said "super key". It also decouples actual PK from the superkey/FK. –  gbn Mar 16 '11 at 11:45
    
It works, thanks :) –  Joril Mar 16 '11 at 23:27
add comment

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

share|improve this answer
    
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 Mar 15 '11 at 22:35
    
OK, can you alter the primary key to have two columns? then your original command should work, i believe. –  SQLRockstar Mar 15 '11 at 22:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.