SQL

UNIQUE in SQL

🔐 The UNIQUE constraint in SQL is used to ensure that all values in a column or a combination of columns are unique. It prevents duplicate values from being inserted into the table, helping maintain data integrity and consistency.

📖 What is the UNIQUE Constraint?

The UNIQUE constraint guarantees that no two rows contain the same value (or combination of values) in the constrained column(s). Unlike a PRIMARY KEY, a table can have multiple UNIQUE constraints.

Information

The exact behavior of NULL values in a UNIQUE constraint varies between database systems. Some allow multiple NULL values, while others handle them differently.

🎯 Why Use UNIQUE?

The UNIQUE constraint is useful whenever duplicate values are not allowed.

📌 Prevent duplicate email addresses.
📌 Ensure usernames are unique.
📌 Avoid duplicate employee IDs or product codes.
📌 Maintain data accuracy.
📌 Improve data integrity.

📝 Basic Syntax

UNIQUE Constraint Syntax

CREATE TABLE table_name
(
    column_name data_type UNIQUE
);

💡 Create a Table with UNIQUE

Create a Students table where every student email address must be unique.

UNIQUE Column Example

CREATE TABLE Students
(
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(150) UNIQUE,
    Department VARCHAR(100)
);

In this example, no two students can have the same email address.

✅ Valid INSERT

This statement succeeds because the email address is unique.

Valid INSERT

INSERT INTO Students
(StudentID, Name, Email, Department)
VALUES
(101, 'Alice', 'alice@example.com', 'Computer Science');

❌ Invalid INSERT

Attempting to insert another row with the same email address causes an error.

Duplicate UNIQUE Value

INSERT INTO Students
(StudentID, Name, Email, Department)
VALUES
(102, 'Bob', 'alice@example.com', 'Mathematics');

Error

The database rejects the second row because the Email value already exists.

🔗 Composite UNIQUE Constraint

A UNIQUE constraint can also apply to multiple columns. In this case, the combination of values must be unique.

Composite UNIQUE Constraint

CREATE TABLE Enrollments
(
    StudentID INT,
    CourseID INT,
    Semester VARCHAR(20),

    CONSTRAINT UQ_Enrollment
    UNIQUE (StudentID, CourseID)
);

A student cannot enroll in the same course more than once, but the same course can be taken by different students.

➕ Add a UNIQUE Constraint to an Existing Table

You can add a UNIQUE constraint after creating a table.

Add UNIQUE Constraint

ALTER TABLE Students
ADD CONSTRAINT UQ_Students_Email
UNIQUE (Email);

➖ Remove a UNIQUE Constraint

Removing a UNIQUE constraint requires database-specific syntax.

Drop UNIQUE Constraint (SQL Server Example)

ALTER TABLE Students
DROP CONSTRAINT UQ_Students_Email;

Important

The syntax for removing a UNIQUE constraint differs among database systems. Some databases require dropping the associated unique index instead.

📊 Example Table

StudentID	Name	Email
101	Alice	alice@example.com
102	Bob	bob@example.com

⚖️ PRIMARY KEY vs UNIQUE

Feature	PRIMARY KEY	UNIQUE
Duplicate Values Allowed	❌ No	❌ No
NULL Values	❌ Not allowed	Depends on the database system.
Number Allowed Per Table	One	Multiple
Typical Purpose	Identify each row.	Prevent duplicate values.

💼 Real-World Example

In an online learning platform, every user must register with a unique email address to ensure each account can be identified correctly.

Users Table

CREATE TABLE Users
(
    UserID INT PRIMARY KEY,
    Username VARCHAR(50) UNIQUE,
    Email VARCHAR(150) UNIQUE,
    PasswordHash VARCHAR(255)
);

This design ensures that both usernames and email addresses remain unique across all users.

⚠️ Common Mistakes

❌ Adding a UNIQUE constraint when duplicate data already exists.
❌ Assuming PRIMARY KEY and UNIQUE are identical.
❌ Forgetting that composite uniqueness applies to the combination of columns.
❌ Not understanding how the chosen database handles NULL values.

Warning

Before adding a UNIQUE constraint to an existing table, verify that duplicate values have been removed. Otherwise, the operation may fail.

⚠️ Best Practices

Best Practice

Apply UNIQUE to columns that require distinct values, use descriptive constraint names, clean existing duplicate data before adding the constraint, combine it with NOT NULL when appropriate, and understand your database system's handling of NULL values.

🚀 Key Points to Remember

📌 UNIQUE prevents duplicate values.
📌 Multiple UNIQUE constraints can exist in a table.
📌 Composite UNIQUE constraints enforce uniqueness across multiple columns.
📌 PRIMARY KEY is always unique, but not every UNIQUE column is a primary key.
📌 Database systems may handle NULL values differently.
📌 UNIQUE helps maintain accurate and reliable data.

>>"Unique values create reliable data, and the UNIQUE constraint ensures that important information is never duplicated."

Summary

✅ The UNIQUE constraint is an essential SQL feature for preventing duplicate values in one or more columns. It improves data quality, supports business rules, and works alongside constraints such as PRIMARY KEY and NOT NULL to maintain a consistent and reliable database.