MySQL UNIQUE Constraint

Introduction

The UNIQUE constraint ensures that all values in a column are different from each other.

It prevents duplicate values from being inserted into the column.

This constraint is commonly used for fields that must remain unique, such as email addresses, usernames, or phone numbers.

---

Why Use a UNIQUE Constraint

In many databases, some fields must contain unique values.

Examples:

• Email address

• Username

• Phone number

• Employee ID

Using the UNIQUE constraint helps maintain data consistency and prevents duplicate records.

---

Syntax of UNIQUE Constraint

Example:

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

In this example, two users cannot have the same email address.

---

Adding a UNIQUE Constraint to an Existing Table

Example:

ALTER TABLE users
ADD UNIQUE(email);

---

Difference Between UNIQUE and NOT NULL

A column with UNIQUE can still contain NULL values unless NOT NULL is also applied.

---

Key Points

• UNIQUE prevents duplicate values

• Multiple columns can have UNIQUE constraints

• A table can have multiple UNIQUE constraints

• Helps maintain data integrity