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?

1 Like

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 …

Primary Key would be the main unique identifier column within a table.
So if it’s your “employees” table - the primary key will be, say, employee_id.

And within the same table, you can have an extra, secondary, unique identifier which is referred to as Candidate Key. That can be a phone_number column - also unique for each employee, but not the main identifier.

In the lesson, it says only one column can be a Primary key but that is wrong! A Primary key can be made up of more than one column. It’s composite but it’s still a single Primary key for that table. I request the admin to correct this in the lesson, thanks!

1 Like

if you find that you don’t have any column that could be a PRIMARY KEY , add one, call it table_id .
(add one, call it table_id .)which means?

Let’s say you have a table that tracks your shopping list, your table will have a column called stuff and one called quantity, the row will look like: pear, 5.
Now none of stuff or quantity can be considered as a candidate key, what do you do?
You make your own id column, call it id, or whatever, set it to be AUTO_INCREMENT In MySQL (may vary in other DB systems), so your row will become 1, pear, 5.

Ideally you can set the pair (stuff, quantity) as an unique primary key but it can be messy, say you just want to add 5 pears twice. It’s up to you.

2 Likes

Actually, there is a keyword PRIMARY KEY you can use in your CREATE TABLE statement to specify the column that will work as a primary key.