PRIMARY KEY and UNIQUE: What is the difference?

A column is called a PRIMARY KEY if it uniquely identifies a specific row in a table of multiple rows. A UNIQUE key is a constraint applied on a column.

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)

Buy Me A Coffee

Found this article helpful? Please consider supporting!

Ram
Ram

I'm a full-stack developer and a software enthusiast who likes to play around with cloud and tech stack out of curiosity. You can connect with me on Medium, Twitter or LinkedIn.

Leave a Reply

Your email address will not be published. Required fields are marked *