MYSQL Tutorial

MySQL UNIQUE

MySQL UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column (or group of columns) are distinct—no duplicates allowed. It’s perfect for fields like usernames, emails, or any data that must be unique.

Syntax

column_name data_type UNIQUE
-- or for multi-column:
UNIQUE (column1, column2)

Defining UNIQUE in CREATE TABLE

CREATE TABLE users (
  user_id   INT AUTO_INCREMENT PRIMARY KEY,
  username  VARCHAR(50) NOT NULL UNIQUE,
  email     VARCHAR(100)    UNIQUE,
  phone     VARCHAR(20)
);

-- Multi-column unique key (e.g., one user can’t have same phone+email)
CREATE TABLE contacts (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  email     VARCHAR(100),
  phone     VARCHAR(20),
  UNIQUE (email, phone)
);

Adding UNIQUE to an Existing Table

ALTER TABLE users
ADD CONSTRAINT uq_username
  UNIQUE (username);

Dropping a UNIQUE Constraint

ALTER TABLE users
DROP INDEX uq_username;

Tip: Before applying UNIQUE on existing data, check for duplicates: SELECT column, COUNT(*) FROM table GROUP BY column HAVING COUNT(*) > 1;

Common Use Cases

Usernames, email addresses, or social security numbers.
Product SKUs or serial numbers.
Composite keys ensuring complex uniqueness rules.

🌟 Enjoyed Learning with Us?

Help others discover Technorank Learning by sharing your honest experience.
Your support inspires us to keep building!

Leave a Google Review

Topics