SQL UNIQUE Constraint

The UNIQUE constraint in SQL is used to ensure that all values in a specified column or combination of columns are unique across the entire table. It prevents duplicate values from being inserted into the table, thus maintaining data integrity.

1. Definition

The UNIQUE constraint ensures that every value in a specified column or combination of columns is unique. Unlike the primary key constraint, which uniquely identifies each row in a table, the unique constraint allows null values.

Example:

// Example of defining a UNIQUE constraint
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    phone_number VARCHAR(15) UNIQUE
);

In this example, the email and phone_number columns in the employees table are defined as UNIQUE, ensuring that each email address and phone number is unique across all rows.

2. Benefits

The UNIQUE constraint offers several benefits:

Ensures data integrity by preventing duplicate values in specified columns.
Provides a mechanism to enforce business rules that require certain attributes to be unique.
Supports efficient indexing for faster data retrieval and query performance.
Helps maintain data consistency and accuracy by avoiding duplicate entries.

3. Usage

To define a UNIQUE constraint, you specify the column(s) within the CREATE TABLE statement and declare them as unique. Alternatively, you can add a UNIQUE constraint after table creation using the ALTER TABLE statement.

Example:

// Example of adding a UNIQUE constraint after table creation
ALTER TABLE employees
ADD CONSTRAINT uk_email UNIQUE (email);

This example adds a UNIQUE constraint named uk_email to the email column in the employees table after the table has been created.

4. Conclusion

The UNIQUE constraint is a powerful tool in SQL for ensuring data integrity and enforcing uniqueness within specified columns. By preventing duplicate values, the UNIQUE constraint helps maintain the accuracy and consistency of database records, contributing to the overall reliability of the database.