PostgreSQL - UNIQUE Constraint

The UNIQUE constraint in PostgreSQL ensures that all values in a column or a group of columns are unique across the table. Unlike a PRIMARY KEY, a table can have multiple UNIQUE constraints and a UNIQUE column can contain NULL values (unless it is also defined as NOT NULL).

• Prevent duplicate values in one or more columns.
• Maintain data uniqueness and consistency.
• Create unique constraints during table creation or using ALTER TABLE.

Syntax

Create a UNIQUE constraint using the CREATE TABLE statement is:

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    CONSTRAINT constraint_name UNIQUE (column_name)
);

Add a UNIQUE constraint:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
UNIQUE (column_name);

Working

The following examples demonstrate how to use the UNIQUE constraint in PostgreSQL

Example 1: Create a UNIQUE Constraint During Table Creation

The following query creates a users table with a UNIQUE constraint on the username column.

Query:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    city VARCHAR(50)
);

INSERT INTO users VALUES
(1, 'james01', 'New York'),
(2, 'emily02', 'Chicago');

Output:

• The username column is defined as UNIQUE.
• Since both usernames are different, the records are inserted successfully.

Example 2: Verify the UNIQUE Constraint

The following query attempts to insert a duplicate username.

Query:

INSERT INTO users
VALUES
(3, 'james01', 'Boston');

Output:

• The username james01 already exists.
• PostgreSQL rejects the insert because it violates the UNIQUE constraint.

Example 3: Add a UNIQUE Constraint to an Existing Table

Firstly, create a suppliers table.

CREATE TABLE suppliers (
    supplier_id INT PRIMARY KEY,
    supplier_name VARCHAR(50),
    email VARCHAR(100)
);

Add a UNIQUE constraint to the email column.

Query:

ALTER TABLE suppliers
ADD CONSTRAINT uq_supplier_email
UNIQUE (email);

Output:

• The ALTER TABLE statement adds a UNIQUE constraint to the email column.
• PostgreSQL now prevents duplicate email addresses.

Example 4: Insert Duplicate Data After Adding the UNIQUE Constraint

Query:

INSERT INTO suppliers VALUES
(101, 'ABC Traders', 'contact@abc.com');

INSERT INTO suppliers VALUES
(102, 'XYZ Supplies', 'contact@abc.com');

Output:

• The email address contact@abc.com already exists.
• PostgreSQL rejects the second insert because it violates the UNIQUE constraint.