TL;DR
- Should a foreign key always refer to the id column of another table? Why or why not? Is there a standard rule for this?
- 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 aunique
size
column as a foreign key fromvehicle_size
table andunique
color
column as the foreign key fromvehicle_color
table.But in
vehicle_user
I used theuser_identifier_id
as a foreign key which refers toid
primary key column inuser_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 ?
CodePudding user response:
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.
CodePudding user response:
A foreign key is a field or a column that is used to establish a link between two tables. A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. There is no rule that it should refer to a id column but the column it refers to should be a Primary Key. In real scenarios, It usually refers to Id coloumn as in most cases it is the Primary Key in the Tables.
CodePudding user response:
A foreign key is basically a column of a different table(it is always of a different table, since that is the role it serves). It is used to join/ get data from a different table. Think of it like say school is a database and there are many different table for different aspects of student.
say by using Admission number 1234, from accounts table you can get the fees and sports table you can get the sports he play.
Now there is no rule that foreign key should be id column, you can keep it whatever you want. But,to use foreign key you should have a matching column in both tables therefore usually id column is only used. As I stated in the above example the only common thing in say sports table and accounts table would be admission number.
admn_no | sports |
--------- ------------
| 1234 | basketball
--------- ---------
| admn_no | fees |
--------- ---------
| 1234 | 1000000 |
--------- ---------
Now say using the query\
select * from accounts join sports using (admn_no);
you will get:
--------- --------- ------------
| admn_no | fees | sports |
--------- --------- ------------
| 1234 | 1000000 | basketball |
--------- --------- ------------
PS: sorry for bad formatting