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.

7 Likes

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!

1 Like

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!

4 Likes

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.

5 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.

4 Likes

Hello, is it possible to join 2 tables using columns that are not primary key? eg. if table A has cols name, address, phone for users in voting precinct A, and table B has name, address and phone for users in voting precinct B, and there are no primary keys in either table, can i do a simple join of both tables on name column?

You could, but names may not be unique values (imagine you have two John Smiths in the same precinct), what would happen then? Your data would get messy and your results would not be accurate. For this reason, it’s better if each voter (if I use your example) also has a unique ID in your table. Then use that value for your JOIN.

Hum, that actually works. Not sure if this is “best practice”, but it does yield the same result with less code.

I googled to reinforce my understanding and I found that:

Candidate key is also called natural key, domain key, or business key. This key is unique. It may not necessarily be primary but it usually is.

Primary key is unique and non-null.

Let’s take an example

create table employees (
   employee_id int not null primary key,
   empssn char(9),
   firstname varchar(50) not null,
   lastname varchar(50) not null,
   gender char(1),
   constraint uk_employees_ssn unique (empssn)
);

Employee SSN here is a candidate key. Business-wise it is natural to think that SSN will not be duplicated. It is possible that an employee doesn’t have an SSN yet or the employee has not disclosed their SSN yet. Therefore it is unique and nullable.

In this example, we have also chosen to make a different field called employee_id the primary key. The theory is to give employees a sequential numeric value. This allows us to change SSN (I learned while doing a project that under certain circumstances SSN can change). Employee ID is called the surrogate key here. user2864740 has a good comment about the relationship between primary and candidate key.

If someone wants further, it is the link:

5 Likes

I don’t understand the structure of this query ie what each line is doing. Seems different from what’s been shown in the course up to this point. Can you kindly explain? thanks.

Could someone please help me finding what’s wrong with this query?

SELECT id, description, COUNT(*)

FROM classes

JOIN students

ON classes.id = students.class_id

GROUP BY classes.description

ORDER BY id;

In the SELECT clause, you have to specify which table each of your columns are from

2 Likes

It should be like this

SELECT classes.id, classes.description, COUNT(*)

FROM classes

JOIN students

ON classes.id = students.class_id

GROUP BY classes.description

ORDER BY classes id;

1 Like

I think candidate keys are the group of keys which are eligible to be primary key having all the eligibilities to be primary keys and Since the primary key can only be one. In my idea primary key can be taken as a subset of the candidate key which in a way is a set of eligible primary keys

1 Like