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

share|improve this question

2 Answers 2

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

share|improve this answer

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.

share|improve this answer

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.