MySQL UNIQUE Constraint

The UNIQUE constraint in MySQL is used to ensure that all values in a column or a combination of columns are unique across all rows in the table. It prevents duplicate entries, making sure that each value in the column (or combination of columns) is distinct.

Syntax

The UNIQUE constraint can be defined in two ways:

1. When Creating a Table

CREATE TABLE table_name (
    column1 datatype UNIQUE,
    column2 datatype,
    ...
);

2. Altering an Existing Table to Add a UNIQUE Constraint

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ...);

column1, column2, ...: Columns on which the UNIQUE constraint will be applied.
constraint_name: A custom name for the constraint (optional when adding the constraint).

Examples

1. Creating a Table with a UNIQUE Constraint

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    phone_number VARCHAR(15)
);

The email column must contain unique values. No two employees can have the same email address.

2. Adding a UNIQUE Constraint to an Existing Table

If you have an existing table and want to ensure uniqueness on a column:

ALTER TABLE employees
ADD CONSTRAINT unique_email UNIQUE (email);

3. Using UNIQUE on Multiple Columns

You can also apply the UNIQUE constraint to a combination of columns. The combination of values across these columns must be unique:

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    product_id INT,
    UNIQUE (customer_id, product_id) -- Ensures no customer orders the same product twice
);

In this case, no customer can order the same product more than once, even if they make multiple orders.

How It Works

If you try to insert a row with a duplicate value in a column (or combination of columns) that has a UNIQUE constraint, MySQL will return an error, and the insert will fail.

Example: Inserting Duplicate Values

-- Attempt to insert duplicate email
INSERT INTO employees (employee_id, email, phone_number)
VALUES (1, 'john.doe@example.com', '123-456-7890');

INSERT INTO employees (employee_id, email, phone_number)
VALUES (2, 'john.doe@example.com', '987-654-3210');

The second insert will fail because the email column must contain unique values.

Unique Index

When a UNIQUE constraint is created, MySQL automatically creates a unique index for the specified column(s). This index helps MySQL efficiently check for duplicates and maintain the uniqueness of the values.

Handling Duplicates with `INSERT IGNORE`

If you want to insert a row but ignore the insertion if a duplicate is found, you can use the INSERT IGNORE statement:

INSERT IGNORE INTO employees (employee_id, email, phone_number)
VALUES (2, 'john.doe@example.com', '987-654-3210');

If the email already exists, the row will be ignored without causing an error.

Behavior with `NULL`

The UNIQUE constraint does not treat NULL as a duplicate. This means that you can have multiple NULL values in a column with a UNIQUE constraint, because NULL is not considered equal to any other NULL.

Example:

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

-- Insert a NULL email
INSERT INTO users (email) VALUES (NULL);

-- Insert another NULL email, which is allowed
INSERT INTO users (email) VALUES (NULL);

Differences Between `PRIMARY KEY` and `UNIQUE`

Primary Key:
- A PRIMARY KEY constraint uniquely identifies each row in a table.
- It implicitly creates a unique index and does not allow NULL values.
Unique Constraint:
- A UNIQUE constraint ensures that all values in a column or a set of columns are distinct.
- It allows NULL values (multiple NULL values are allowed).

Key Considerations

Single vs. Multiple Columns:
- The UNIQUE constraint can be applied to a single column or a combination of columns.
- When applied to multiple columns, the combination of values across those columns must be unique.
NULL Handling:
- The UNIQUE constraint allows multiple NULL values, whereas PRIMARY KEY does not.
Indexing:
- A UNIQUE constraint automatically creates a unique index on the specified columns, improving lookup performance but also consuming additional space.

Conclusion

The UNIQUE constraint is essential for ensuring data integrity by preventing duplicate values in one or more columns. It is widely used to enforce uniqueness for fields like email addresses, usernames, or any other column where duplicate entries would be undesirable. By using the UNIQUE constraint, MySQL efficiently ensures that the data remains consistent and prevents unnecessary duplication.

Souy Soeng

MySQL UNIQUE Constraint