3

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.

5

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

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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