Difference Between Primary Key and Unique Key

 

	Primary Key	Unique Key
Basic Purpose	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 but is used when a primary key is not present.
NULL Values	We cannot store NULL values in the primary key column.	We can store NULL values in the unique key column, but only one NULL is allowed.
Purpose	It enforces entity integrity by ensuring each record has a unique identifier.	It enforces unique data by ensuring that no duplicate values (except NULL) are stored in the column.
Index	The primary key, by default, creates a clustered index.	The unique key, by default, creates a non-clustered index.
Number of Keys	Each table supports only one primary key.	A table can have multiple unique keys.
Value Modification	Cannot modify or delete the primary key values once set.	Can modify the values in a unique key column.
Uses	It is used to identify each record uniquely in the table.	It prevents duplicate values in a column, except for NULL.
Syntax Example	CREATE TABLE Employee ( Id INT PRIMARY KEY, name VARCHAR(150), address VARCHAR(250) );	CREATE TABLE Person ( Id INT UNIQUE, name VARCHAR(150), address VARCHAR(250) );

Key Differences:

1. Basic Purpose:
• Primary Key: Ensures that each record in the table has a unique identifier, which is essential for entity integrity. Every table can only have one primary key.
• Unique Key: Ensures that all values in a column are unique, except for NULL values. It helps to prevent duplicate entries. A table can have multiple unique keys.
2. NULL Values:
• Primary Key: The primary key column cannot have NULL values, as it must uniquely identify each row in the table.
• Unique Key: The unique key column can have NULL values, but only one NULL is allowed (in case of multiple NULLs, only the first NULL is allowed).
3. Purpose:
• Primary Key: Its main purpose is to enforce entity integrity, ensuring that each row has a unique, non-NULL identifier.
• Unique Key: Its purpose is to enforce unique data, preventing duplicate entries in the column, except for NULL values.
4. Index:
• Primary Key: Automatically creates a clustered index on the column(s) it is defined on. This means that the rows in the table are physically stored in the order of the primary key.
• Unique Key: By default, creates a non-clustered index, meaning the rows are not stored in any specific order according to the unique key. This index is used to speed up queries.
5. Number of Keys:
• Primary Key: A table can have only one primary key. The primary key can be made up of a single column or a combination of columns (composite primary key).
• Unique Key: A table can have multiple unique keys. You can define more than one unique constraint on a table, ensuring multiple columns have unique values.
6. Value Modification:
• Primary Key: Once set, the primary key cannot be modified or deleted, as it is used to uniquely identify records. Changes to primary key values could affect data integrity.
• Unique Key: The values in a unique key column can be modified as long as they remain unique. You can change the values in a unique key column unless other constraints are in place.
7. Uses:
• Primary Key: Used to identify each record uniquely in the table, and it is crucial for defining relationships between tables (e.g., parent-child relationships via foreign keys).
• Unique Key: Used to ensure that the column contains only unique values (except for NULLs) and is generally applied when a column should have unique data but does not need to be the main identifier of the table.

Example of Primary and Unique Key Usage:

• Primary Key:

CREATE TABLE Employee (

    Id INT PRIMARY KEY,    -- Primary key to uniquely identify each employee

    name VARCHAR(150),

    address VARCHAR(250)

);

• Unique Key:

CREATE TABLE Person (

    Id INT UNIQUE,         -- Unique key to ensure the 'Id' column contains only unique values

    name VARCHAR(150),

    address VARCHAR(250)

);