SQL UNIQUE Constraint

Introduction to UNIQUE Constraint

The UNIQUE constraint in SQL ensures that all values in a column or a combination of columns are different. It is used to enforce the uniqueness of values, preventing duplicate entries in the specified columns.

Why Use UNIQUE?

The UNIQUE constraint is essential when you want to ensure that a particular column (or a combination of columns) contains unique data across all rows in a table. It is commonly used for fields like email addresses, usernames, or any other data that must be unique for each record.

Syntax

The UNIQUE constraint can be defined when creating a table or adding it to an existing table. Here’s the basic syntax:

CREATE TABLE table_name (
    column1 datatype UNIQUE,
    column2 datatype,
    ...
);

You can also define a unique constraint on multiple columns:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    CONSTRAINT constraint_name UNIQUE (column1, column2)
);

Example with Single Column

Let’s consider an example where we create a table called users with a UNIQUE constraint on the email column to ensure no two users can have the same email address:

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(100),
    email VARCHAR(255) UNIQUE
);

If you attempt to insert two users with the same email address, SQL will throw an error:

INSERT INTO users (id, username, email) VALUES (1, 'JohnDoe', 'john@example.com');
INSERT INTO users (id, username, email) VALUES (2, 'JaneDoe', 'john@example.com');
-- This will result in an error because the email 'john@example.com' is already in use.

Example with Multiple Columns

You can also enforce uniqueness across a combination of columns. For instance, if you want to ensure that a combination of username and email is unique, you can define the constraint as follows:

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(100),
    email VARCHAR(255),
    CONSTRAINT unique_user_email UNIQUE (username, email)
);

This ensures that the combination of username and email is unique for each record.

Adding UNIQUE to an Existing Table

If you need to add a UNIQUE constraint to an existing table, use the ALTER TABLE statement:

ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);

Removing UNIQUE Constraint

If you need to remove a UNIQUE constraint, use the ALTER TABLE statement along with DROP CONSTRAINT:

ALTER TABLE users
DROP CONSTRAINT unique_email;

Conclusion

The UNIQUE constraint is a fundamental tool in SQL for maintaining the uniqueness of data in your tables. Whether applied to a single column or a combination of columns, it ensures that no duplicate values exist where uniqueness is required.