SQL UNIQUE Constraint

The UNIQUE constraint in SQL ensures that all values in a specified column or combination of columns are distinct. It prevents duplicate entries in the table, making it a crucial tool for maintaining data integrity. Unlike the PRIMARY KEY constraint, you can have multiple UNIQUE constraints in a table.

Why Use the UNIQUE Constraint?

The UNIQUE constraint is essential for ensuring data accuracy and avoiding redundancy in your database. Here's why:

• Prevent Duplicate Data: Ensures that no two rows have the same value in the specified column(s).
• Maintain Data Integrity: Useful for columns like email addresses, usernames, or IDs where values must be distinct.
• Flexible Design: Allows multiple UNIQUE constraints per table to enforce uniqueness across different fields.

Apply UNIQUE Constraint

The UNIQUE constraint can be applied during table creation or added to an existing table. It can also be named explicitly for better identification.

1. Adding UNIQUE on Table Creation

When creating a table, you can define the UNIQUE constraint for a single column or a combination of columns.

Example: Single Column

1
2
3
4
5
CREATE TABLE Employees (
    EmployeeID INT NOT NULL UNIQUE,
    Email VARCHAR(255) UNIQUE,
    Name VARCHAR(100)
);

• Ensures that EmployeeID and Email columns have unique values.

Example: Multiple Columns

1
2
3
4
5
6
CREATE TABLE Orders (
    OrderID INT NOT NULL,
    CustomerID INT NOT NULL,
    ProductID INT NOT NULL,
    CONSTRAINT UC_Order UNIQUE (CustomerID, ProductID)
);

• Combines CustomerID and ProductID to enforce uniqueness for customer-product combinations.

2. Adding UNIQUE to an Existing Table

If the table already exists, you can add a UNIQUE constraint using the ALTER TABLE statement.

Example: Single Column

1
2
ALTER TABLE Employees  
ADD UNIQUE (Email);

Example: Multiple Columns

1
2
ALTER TABLE Orders  
ADD CONSTRAINT UC_Order UNIQUE (CustomerID, ProductID);

3. Naming a UNIQUE Constraint

Explicitly naming a UNIQUE constraint helps with database management and debugging.

Example

1
2
3
4
5
CREATE TABLE Products (
    ProductID INT NOT NULL,
    ProductCode VARCHAR(50) NOT NULL,
    CONSTRAINT UC_ProductCode UNIQUE (ProductCode)
);

4. Removing a UNIQUE Constraint

To remove a UNIQUE constraint, you need to drop it using the appropriate SQL command.

Example: MySQL

1
2
ALTER TABLE Employees  
DROP INDEX UC_Email;

Example: SQL Server / Oracle

1
2
ALTER TABLE Employees  
DROP CONSTRAINT UC_Email;

Key Differences Between UNIQUE and PRIMARY KEY

• Multiple Constraints: A table can have multiple UNIQUE constraints but only one PRIMARY KEY.
• NULL Values: UNIQUE columns can accept NULL values (depending on the database), while PRIMARY KEY columns cannot.
• Purpose: Both enforce uniqueness, but PRIMARY KEY is used to uniquely identify each row in a table.

Benefits of Using UNIQUE Constraint

• Data Accuracy: Prevents duplicate entries in critical fields.
• Enhanced Querying: Ensures reliable results when filtering by unique columns.
• Flexible Implementation: Supports multiple unique constraints in a single table.
• Database Integrity: Helps enforce business rules at the database level.

Common Use Cases

1. User Authentication: Ensure unique usernames or email addresses in a user table.
2. Inventory Management: Enforce unique product codes in an inventory table.
3. Order Management: Prevent duplicate customer-product order entries.
4. Data Deduplication: Avoid storing redundant data in the database.

Key Points to Remember

• The UNIQUE constraint enforces distinct values in one or more columns.
• It can be applied during table creation or added to an existing table.
• Multiple UNIQUE constraints are allowed per table, unlike PRIMARY KEY.
• Always use descriptive names when defining UNIQUE constraints to simplify management.
• Use DROP INDEX or DROP CONSTRAINT to remove a UNIQUE constraint.