UNIQUE constraints in MySQL

Introduction

A UNIQUE constraint makes sure that no two rows in a table have the same value in certain columns. It helps keep data clean and avoids duplicates.

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 contact list.

Syntax

MySQL

CREATE TABLE table_name (
  column_name datatype UNIQUE
);

-- Or add UNIQUE constraint after table creation:
ALTER TABLE table_name ADD UNIQUE (column_name);

You can add UNIQUE to one or more columns to prevent duplicate values.

UNIQUE constraints allow NULL values; multiple NULLs are permitted in MySQL.

Examples

Creates a users table where each email must be unique.

MySQL

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

Adds a UNIQUE constraint to the product_code column in the products table.

MySQL

ALTER TABLE products ADD UNIQUE (product_code);

Ensures that the combination of first and last names is unique.

MySQL

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 column. The third insert fails because the phone number is already used.

MySQL

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  phone VARCHAR(15) UNIQUE
);

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

Important Notes

UNIQUE constraints help keep your data accurate by stopping duplicates.

If you try to insert a duplicate value, MySQL will give an error and stop the insert.

You can have multiple UNIQUE constraints on different columns in the same table.

Summary

UNIQUE constraints prevent duplicate values in one or more columns.

They help keep data clean and consistent.

Trying to insert duplicates causes an error.