What is PRIMARY KEY, and How it differ from a UNIQUE KEY constraints in SQL

When working with databases, understanding key constraints is essential for ensuring data integrity. In this post, I’ll explain what a PRIMARY KEY is, how it differs from a UNIQUE KEY, and when to use them.

1. What is a PRIMARY KEY?

A PRIMARY KEY is a column (or a set of columns) in a table that uniquely identifies each row. It ensures that no two rows have the same primary key value and that the key value is not null.

Key Characteristics of PRIMARY KEY:

• Uniquely identifies each row in a table.
• Cannot contain NULL values.
• A table can only have one PRIMARY KEY.

Example:

-- Defining a PRIMARY KEY on 'customer_id'
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

2. What is a UNIQUE KEY?

A UNIQUE KEY constraint also ensures that all values in a column (or a set of columns) are unique. However, unlike the primary key, a unique key can contain NULL values, and a table can have multiple unique keys.

Key Characteristics of UNIQUE KEY:

• Ensures uniqueness of values in a column or group of columns.
• Allows NULL values (but only one NULL per column).
• A table can have multiple UNIQUE KEY constraints.

Example:

-- Defining a UNIQUE KEY on 'email'
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

3. Key Differences Between PRIMARY KEY and UNIQUE KEY

While both PRIMARY KEY and UNIQUE KEY constraints enforce uniqueness, there are some important differences:

PRIMARY KEY	UNIQUE KEY
Ensures unique values across the table and does not allow NULL.	Ensures unique values but allows one NULL value.
Each table can only have one PRIMARY KEY.	A table can have multiple UNIQUE KEY constraints.
By default, creates a clustered index.	By default, creates a non-clustered index.

4. When to Use PRIMARY KEY and UNIQUE KEY

Use a PRIMARY KEY when you want to uniquely identify each record in a table and ensure that the key value is never null. Use a UNIQUE KEY when you need to ensure uniqueness but allow for one or more NULL values, or when you need more than one unique constraint in a table.

Example:

-- Using both PRIMARY KEY and UNIQUE KEY in the same table
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

Conclusion

Both PRIMARY KEY and UNIQUE KEY constraints are essential tools for maintaining data integrity. Understanding their differences and how to use them effectively will help you design better database schemas.