up vote 1 down vote favorite
share [fb]

I have a two columns should references two columns in another table. And those two columns are not primary key. I think I can't use foreign key, because it can only references primary keys.

link|improve this question
feedback

2 Answers

You can't use a foreign key. It seems like your model is not normalized. Review your data model and try to get it to third normal form.

If those two columns form a natural (and unique) key, then add a unique key containing those two rules. Then reference the primary key of the other table. Remove the columns from the table that needs to reference the columns and use a join when you need the columns

In the above solution, the primary key is a surrogate key for your two columns. I usually use a surrogate key for table where the natural key consists of two or more fields. If any part of the natural key could change, then a surrogate is usually preferred.

If the two columns you want to reference are not unique in second table, then you could move them to a third table where they are unique. Then use a foreign key reference from both tables to the new table.

link|improve this answer
feedback

Postgres supports foreign keys to unique indexes (a primary key is a special case of a unique key). From the documentation

The referenced columns must be the columns of a non-deferrable unique or primary key constraint in the referenced table

link|improve this answer
feedback

Your Answer

 
or
required, but never shown

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