SQL UNIQUE Constraint

Last Updated : 31 Aug, 2026

The UNIQUE constraint in SQL prevents duplicate entries in specified column(s) while still allowing multiple NULL values. It helps maintain data accuracy without the strict non-NULL requirement of a PRIMARY key.

  • Supports single or multi-column definitions.
  • Can be added or removed using ALTER TABLE.
  • Index creation depends on the database.

Query:

CREATE TABLE employees (
    emp_id INT,
    email VARCHAR(100) UNIQUE
);

INSERT INTO employees (emp_id, email)
VALUES
    (1, 'alex@example.com'),
    (2, NULL),
    (3, NULL);

Output:

Screenshot-2026-08-24-122559
  • UNIQUE prevents duplicates; only non-NULL values must remain strictly distinct.
  • Multiple NULLs allowed because SQL treats every NULL as unique.

Query:

INSERT INTO employees (emp_id, email)
VALUES (4, 'alex@example.com');

Error:

Screenshot-2026-08-24-122931
  • The value 'alex@example.com' already exists in the email column.
  • UNIQUE constraint blocks inserting a duplicate, so the query fails.

Syntax

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

Example

In these examples, we demonstrate the implementation of SQL UNIQUE constraint:

Example 1: Creating a Table with UNIQUE Constraints

Let's create a Customers table where the Email column must be unique.

Query:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    country VARCHAR(50)
);

-- Insert data into customers table
INSERT INTO customers (customer_id, name, email, country)
VALUES
    (1, 'John Doe', 'john.doe@example.com', 'USA'),
    (2, 'Jane Smith', 'jane.smith@example.com', 'Canada');

Output:

Screenshot-2026-08-24-123039
  • The Email column uses a UNIQUE constraint, so no two customers can share the same email.
  • Both inserted rows succeed because each email value is different.

Query:

-- This will fail because the email already exists
INSERT INTO customers (customer_id, name, email, country)
VALUES
    (3, 'Alice Johnson', 'john.doe@example.com', 'UK');

Error:

Screenshot-2026-08-24-123129
  • The insert fails because the email value already exists in a UNIQUE-restricted column.
  • UNIQUE prevents duplicate non-NULL emails, so the row is rejected.

Example 2: Using UNIQUE with Multiple Columns

We can also apply the UNIQUE constraint to multiple columns to ensure that the combination of those columns is unique.

Query:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    order_date DATE,
    UNIQUE (customer_id, product_id)
);

--Insert data into orders table
INSERT INTO orders (order_id, customer_id, product_id, order_date)
(1, 101, 501, '2024-01-10'),
(2, 102, 501, '2024-01-12');

Output:

Screenshot-2026-08-24-123225
  • The UNIQUE(customer_id, product_id) constraint ensures that the same customer cannot order the same product more than once.
  • Both inserted rows are valid because each customer_id and product_id pair is different.

Query:

-- This will fail because the CustomerID-ProductID pair already exists
INSERT INTO orders (order_id, customer_id, product_id, order_date)
VALUES
    (3, 101, 501, '2024-01-15');

Error:

Screenshot-2026-08-24-123303
  • The pair (101, 501) already exists, violating the UNIQUE(customer_id, product_id) constraint.
  • UNIQUE prevents the same customer from ordering the same product twice, so the insert fails.
Comment

Explore