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.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Postgres supports foreign keys to unique indexes (a primary key is a special case of a unique key). From the documentation
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.