SQL UNIQUE Constraint

Welcome to The Coding College! In this guide, we will delve into the SQL UNIQUE Constraint, its purpose, usage, and practical examples to help you implement it effectively in your database designs.

What is the UNIQUE Constraint?

The UNIQUE constraint ensures that all values in a column (or a combination of columns) are distinct. It prevents duplicate entries in the specified column, maintaining data integrity and uniqueness.

Unlike the PRIMARY KEY, which also ensures uniqueness, a table can have multiple UNIQUE constraints, while it can only have one PRIMARY KEY.

Why Use the UNIQUE Constraint?

  1. Data Integrity: Avoids duplicate records in critical columns such as email addresses or phone numbers.
  2. Custom Constraints: Allows multiple unique attributes in the same table.
  3. Flexible Design: Supports both single-column and multi-column uniqueness.

Syntax for the UNIQUE Constraint

1. Adding UNIQUE During Table Creation

CREATE TABLE Users (  
    UserID INT PRIMARY KEY,  
    Username VARCHAR(50) UNIQUE,  
    Email VARCHAR(100) UNIQUE  
);
  • In this example:
    • Username and Email must have unique values.

2. Adding UNIQUE to Existing Tables

ALTER TABLE Users  
ADD CONSTRAINT Unique_Email UNIQUE (Email);

3. Composite UNIQUE Constraint

You can enforce uniqueness across multiple columns:

CREATE TABLE Orders (  
    OrderID INT,  
    ProductID INT,  
    CustomerID INT,  
    UNIQUE (ProductID, CustomerID)  
);
  • This ensures that a customer cannot order the same product more than once.

Examples of UNIQUE Constraint

1. Single-Column UNIQUE

CREATE TABLE Employees (  
    EmployeeID INT PRIMARY KEY,  
    EmployeeCode VARCHAR(10) UNIQUE  
);

Insert Example:

INSERT INTO Employees (EmployeeID, EmployeeCode)  
VALUES (1, 'EMP001');  -- Valid  

INSERT INTO Employees (EmployeeID, EmployeeCode)  
VALUES (2, 'EMP001');  -- Error: Duplicate entry for 'EmployeeCode'

2. Multi-Column UNIQUE

CREATE TABLE Enrollments (  
    StudentID INT,  
    CourseID INT,  
    EnrollmentDate DATE,  
    UNIQUE (StudentID, CourseID)  
);

Insert Example:

INSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate)  
VALUES (1, 101, '2024-12-01');  -- Valid  

INSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate)  
VALUES (1, 101, '2024-12-02');  -- Error: Duplicate entry for StudentID and CourseID

Removing UNIQUE Constraints

If you need to drop a UNIQUE constraint from an existing table:

ALTER TABLE Users  
DROP CONSTRAINT Unique_Email;

For some database systems, you may need to know the exact name of the constraint, which can be retrieved using schema queries.

Advantages of UNIQUE Constraint

  1. Improved Data Integrity: Ensures columns contain distinct values.
  2. Flexible Implementation: Can be applied to multiple columns in the same table.
  3. Efficient Indexing: Most databases automatically create indexes for UNIQUE columns, improving query performance.

Common Errors

  1. Violation of UNIQUE Constraint:
    • Error: Duplicate entry for column.
    • Solution: Validate data before insertion.
  2. Dropping a Dependent UNIQUE Constraint:
    • Error: Cannot drop a column used in a UNIQUE constraint.
    • Solution: Drop the constraint before altering or dropping the column.
  3. Composite Constraint Misuse:
    • Error: Duplicate entry for combination of columns.
    • Solution: Ensure unique combinations are maintained.

Best Practices

  • Use Meaningful Names: Name constraints descriptively for easier management. 
ALTER TABLE Users  
ADD CONSTRAINT Unique_Username UNIQUE (Username);
  • Plan for Growth: Use UNIQUE constraints on attributes likely to expand, such as usernames or IDs.
  • Test for Violations: Regularly test the database for any UNIQUE constraint violations.

Conclusion

The SQL UNIQUE Constraint is a vital tool for maintaining the uniqueness of data in your database. Whether used for single columns or across multiple columns, it ensures data reliability and integrity.

Leave a Comment