Non-unique multicolumn foreign key

Question

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

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 08:48
Or just add the line unique (id, topic_id), in your "create table"-command for comments. — j.p., Mar 16 '11 at 08:50

score 5 · Accepted Answer · answered Mar 16 '11 at 05:27

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)

answered Mar 16 '11 at 05:27

gbn

69,809
8
163
243

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

SQLRockstar · Answer 2 · 2011-03-15T22:27:22.997

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

edited Mar 15 '11 at 22:27

answered Mar 15 '11 at 22:20

SQLRockstar

6,359
26
48

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

Non-unique multicolumn foreign key

2 Answers2

Linked