Is it possible for a table to have more than one unique identifier column?

Question

Is it possible for a table to have more than one unique identifier column, like an id column?

Answer

Yes, it is possible for a table to have more than one column which can uniquely identify a row of data. A column that can uniquely identify a record of data is known as a "Candidate Key". Tables can have multiple "Candidate Key"s, each of which could potentially be the "Primary Key", but there must only be one "Primary Key" per table. Usually, the column chosen as the "Primary Key" follows the naming convention like customer_id or product_id.

For example, say that we had a table of employee records, with the columns employee_id and phone_number. Every employee has a unique employee_id value, and a unique phone_number value. Both of these columns can be unique identifiers for a row, so they are "Candidate keys", but the "Primary Key" would most likely be set to employee_id.

18 Likes

Since column can have many candidate key. Is the choice of primary key essentially depends on us or it has to be fixed?

1 Like

In case anyone else is also wondering about this:
A table could have many columns that are candidate keys, but only one PRIMARY KEY.
The choice is completely up to us (we create those tables) but as the answer says there is usually one named id that’s more suitable.
Imagine 2 tables: employees and tasks.
employees has as “Candidate Key” the id, and the phone number. We make a JOIN between these two tables.
Now which “Candidate Key” would you put in the tasks table?
Hypotetical row:
|task_id| employee |description|
|  1  |    1    |“make coffee”|    or
|  1  |3929296528 |“make coffee”|   ?

But when the situation is to choose between a serial id(1, 2, 3, …) and a SSN(Social Security Number) id, I’d recommend you to choose the SSN, although using the serial wouldn’t be an error either.

One last thing: if you find that you don’t have any column that could be a PRIMARY KEY, add one, call it table_id.

Select *
from students,classes on students.class_id
where students.class_id = classes.id
;

Other than all columns being displayed, and the students.class_id not ‘overwriting’ the classes.id, what is the difference on this process, other than providing an ability to expand the WHERE?

but the "Primary Key" would most likely be set to employee_id .

What does it mean by setting “Primary Key”? Is it just a concept rather than a line commanding a designated column as “Primary Key”? Thanks!

Hy to all I got a little bit confused what is difference between primary Key and Candidate key …