PRIMARY KEY and UNIQUE: What is the difference?

What is a Primary Key?

A column in a table (an attribute in an entity) is called a PRIMARY KEY if it uniquely identifies a specific row in a table of multiple rows.

For example an Identifier column (Employee ID, Student ID etc.) can uniquely identify a student or an employee in a data set and one can never repeat. Hence they can be set as Primary Keys.

ALTER TABLE employees
ADD PRIMARY KEY (employee_id);

Sometimes you can also create a Primary Key combining two or more columns of a table. In such case it is called a Composite Key. For example, an Account Number and an Email Address may uniquely identify a user record.

ALTER TABLE my_account
ADD PRIMARY KEY (account_number, email_address);

Primary Key doesn’t allow NULL values. You MUST add a value to column marked Primary Key. Also, Querying is faster over a Primary Key column since you have a Clustered Index created by default.

What is UNIQUE?

On the other hand, UNIQUE is a constraint applied on the data of a column. A constraint is a condition that is applied on the column, that validates any insertion into that column.

ALTER TABLE users
ADD CONSTRAINT uc_email UNIQUE (email);

If you create a UNIQUE constraint over a column then you must ensure that all data inserted into that column is unique. No two rows can contain same data for that column that is marked UNIQUE.

But a column with UNIQUE constraint can contain NULL values.

To summarize –

1. A UNIQUE key means that the column must not contain any duplicate values, meaning all the values in the column must be UNIQUE.
2. A column with UNIQUE key can accept NULL as a valid value (for only once in the column)
3. A PRIMARY key means that the column must be UNIQUE and no NULL values are allowed (even once)
4. Both ensure data integrity for a table by enforcing uniqueness and not null (for Primary Key columns)