Search code examples
sqljoinforeign-keysprimary-keyunique

SQL foreign key on primary key or unique column

Suppose I have two tables, table A and table B, and that table A has the following columns:

COLUMNS (id INT PRIMARY KEY, name VARCHAR(200) UNIQUE)

I want to create a column in table B which references a row of table A using a foreign key. Most of the time when I look up a row in table B I will also want to retrieve the name field in the row of table A that it references. Speed of lookups (but not insertions) is a concern.

Would it be better to use the primary key of table A as the foreign key and then use a JOIN to bring in the value of the name field, or would it be better to use the name field as the foreign key so that that data is already present when looking up a row in table B?

Solution

  • Usually the name can change. Maybe someone finds a better name or there was a typo in the name that must be corrected. Whereas the PK should never change. Always use the ID as FK. This is how a lookup table works.

    If you use an identity column as PK, the IDs will be generated automatically and cannot be changed. It is mostly a good idea to have a meaningless PK. Meaningful columns tend to be subject of edits.