SQL UNIQUE Constraint

The SQL UNIQUE constraint ensures that all values in a column or a group of columns are distinct from each other. This means that no two rows can have the same value in that column or combination of columns.

Syntax

The basic syntax for creating a UNIQUE constraint is:

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

You can also add the UNIQUE constraint to an existing table:

ALTER TABLE table_name
ADD CONSTRAINT unique_constraint_name UNIQUE (column1, column2, ...);

Example

Let's create a table for storing employee information where we want to ensure that each employee's email address is unique.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100) UNIQUE
);

In this example:

• EmployeeID is the primary key, which automatically has a unique constraint.
• Email has an additional UNIQUE constraint to ensure that no two employees can have the same email address.

Inserting Data

Let's insert some data into the Employees table:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Email)
VALUES (1, 'John', 'Doe', 'john.doe@example.com'),
       (2, 'Jane', 'Smith', 'jane.smith@example.com');

This will work because both email addresses are unique.

Now, let's try inserting a duplicate email:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Email)
VALUES (3, 'Alice', 'Johnson', 'john.doe@example.com');

Output

The second insert statement will fail and produce an error like:

ERROR: duplicate key value violates unique constraint "employees_email_key"
DETAIL: Key (email)=(john.doe@example.com) already exists.

This error message indicates that the unique constraint on the Email column was violated because an email address that already exists was attempted to be inserted again.

The UNIQUE constraint helps maintain data integrity by preventing duplicate entries in specified columns.