UNIQUE constraint in SQL

Introduction

The UNIQUE constraint makes sure that no two rows in a table have the same value in a specific column or group of columns.

When you want to make sure each email address in a user list is different.

When you want to prevent duplicate product codes in an inventory.

When you want to ensure each username in a system is unique.

When you want to avoid repeated phone numbers in a contacts table.

Syntax

SQL

CREATE TABLE table_name (
  column_name data_type UNIQUE
);

-- Or add UNIQUE to existing column:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);

The UNIQUE constraint can be added when creating a table or after with ALTER TABLE.

It can apply to one or more columns to make a combined unique key.

Examples

This creates a Users table where each email must be unique.

SQL

CREATE TABLE Users (
  id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);

This adds a UNIQUE constraint to the product_code column in an existing Products table.

SQL

ALTER TABLE Products
ADD CONSTRAINT unique_product_code UNIQUE (product_code);

This ensures that the combination of first and last name is unique in Employees.

SQL

CREATE TABLE Employees (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  UNIQUE (first_name, last_name)
);

Sample Program

This creates a Customers table with a UNIQUE phone_number. The third insert fails because the phone number is already used.

SQL

CREATE TABLE Customers (
  customer_id INT PRIMARY KEY,
  phone_number VARCHAR(20) UNIQUE
);

INSERT INTO Customers (customer_id, phone_number) VALUES (1, '123-456-7890');
INSERT INTO Customers (customer_id, phone_number) VALUES (2, '987-654-3210');
-- The next insert will fail because phone_number '123-456-7890' already exists
INSERT INTO Customers (customer_id, phone_number) VALUES (3, '123-456-7890');

Important Notes

UNIQUE constraints allow NULL values, but some databases treat multiple NULLs as unique or not unique differently.

If you try to insert a duplicate value in a UNIQUE column, the database will give an error and stop the insert.

UNIQUE constraints help keep your data clean by preventing duplicates.

Summary

UNIQUE constraint prevents duplicate values in a column or group of columns.

It can be set when creating a table or added later with ALTER TABLE.

Trying to insert duplicates in UNIQUE columns causes errors.