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 –
- A UNIQUE key means that the column must not contain any duplicate values, meaning all the values in the column must be UNIQUE.
- A column with UNIQUE key can accept NULL as a valid value (for only once in the column)
- A PRIMARY key means that the column must be UNIQUE and no NULL values are allowed (even once)
- Both ensure data integrity for a table by enforcing uniqueness and not null (for Primary Key columns)
