Which column for foreign key: id or any other column and why?

Question

TL;DR

1. Should a foreign key always refer to the id column of another table? Why or why not? Is there a standard rule for this?
2. Is there a cost associated with using any other unique column other than id column for foreign key? Performance / storage? How significant? Is it frowned in the industry?

Example: this is the schema for my sample problem:

In my schema sometimes I use id column as the foreign key and sometimes use some other data column.

• In vehicle_detail table I use a unique size column as a foreign key from vehicle_size table and unique color column as the foreign key from vehicle_color table.

• But in vehicle_user I used the user_identifier_id as a foreign key which refers to id primary key column in user_identifier table.

Which is the correct way?

On a side note, I don't have id columns for the garage_level, garage_spaceid, vehicle_garage_status and vehicle_parking_status tables because they only have one column which is the primary key and the data they store is just at most 15 rows in each table and it is probably never going to change. Should I still have an id column in those ?

Answer 1

A foreign key has to target a primary key or unique constraint. It is normal to reference the primary key, because you typically want to reference an individual row in another table, and the primary key is the identifier of a table row.

From a technical point of view, it does not matter whether a foreign key references the primary key or another unique constraint, because in PostgreSQL both are implemented in the same way, using a unique index.

As to your concrete examples, there is nothing wrong with having the unique size column of vehicle_size be the target of a foreign key, although it begs the question why you didn't make size the primary key and omit the id column altogether. There is no need for each table to have an id column that is the automatically generated numeric primary key, except that there may be ORMs and other software that expect that.