MySQL UNIQUE Constraint

MySQL UNIQUE Constraint

The UNIQUE constraint in MySQL ensures that all values in a column are distinct.
It is used to prevent duplicate values in a column while allowing NULL values (except in some versions where only one NULL is allowed).

🔹 Syntax (During Table Creation)

• Can be applied to single or multiple columns.

• Can also define table-level UNIQUE constraint for multiple columns.

🔹 Syntax (Using ALTER TABLE)

• Adds a UNIQUE constraint to an existing column.

🔹 Example 1: Unique Column

• email column must have unique values; duplicates are not allowed.

🔹 Example 2: Table-Level UNIQUE Constraint (Multiple Columns)

• Combination of first_name + last_name must be unique.

• Two employees can have the same first name or last name individually, but not the same combination.

🔹 Example 3: Adding UNIQUE to Existing Column

• Ensures that username values are unique.

🔹 Key Points

1. UNIQUE ensures distinct values in a column or a set of columns.

2. Multiple UNIQUE constraints can exist in a table.

3. Allows NULL values, but duplicate non-NULL values are not allowed.

4. Often used for emails, usernames, or IDs to avoid duplicates.

5. Can be applied during table creation or added later using ALTER TABLE.