Primary Key vs. Unique Key Comparison Chart
The following comparison chart explains their main differences in a quick manner:
| Comparison Basis | Primary Key | Unique Key |
|---|---|---|
| Basic | The primary key is used as a unique identifier for each record in the table. | The unique key is also a unique identifier for records when the primary key is not present in the table. |
| NULL | We cannot store NULL values in the primary key column. | We can store NULL value in the unique key column, but only one NULL is allowed. |
| Purpose | It enforces entity integrity. | It enforces unique data. |
| Index | The primary key, by default, creates clustered index. | The unique key, by default, creates a non-clustered index. |
| Number of Key | Each table supports only one primary key. | A table can have more than one unique key. |
| Value Modification | We cannot change or delete the primary key values. | We can modify the unique key column values. |
| Uses | It is used to identify each record in the table. | It prevents storing duplicate entries in a column except for a NULL value. |
| Syntax | We can create a primary key column in the table using the below syntax:
CREATE TABLE Employee ( Id INT PRIMARY KEY, name VARCHAR(150), address VARCHAR(250) ) |
We can create a unique key column in the table using the below syntax:
CREATE TABLE Person ( Id INT UNIQUE, name VARCHAR(150), address VARCHAR(250) ) |