MySQL UNIQUE Constraint

MySQL UNIQUE Constraint: Preventing Duplicate Column Values

The MySQL UNIQUE constraint ensures that no two rows in a table share the same value in a column or combination of columns. Unlike a PRIMARY KEY, a table can have multiple UNIQUE constraints, and unique columns can allow NULL values.

Single-Column vs. Composite UNIQUE Syntax

You can apply UNIQUE to a single column (such as email) or across multiple columns together (such as preventing duplicate (customer_id, product_id) rows in a wishlist):

CREATE TABLE demo_wishlist (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    CONSTRAINT uq_demo_wishlist_pair UNIQUE (customer_id, product_id)
);

Practical SQL Example

This example adds a unique email constraint on demo_customers and demonstrates how INSERT ... ON DUPLICATE KEY UPDATE handles conflicts cleanly:

ALTER TABLE demo_customers
    ADD CONSTRAINT uq_demo_customers_email UNIQUE (email);

-- Insert or update existing customer if email already exists
INSERT INTO demo_customers (first_name, email, city)
VALUES ('Jane', '[email protected]', 'Seattle')
ON DUPLICATE KEY UPDATE
    city = VALUES(city);

Common Mistakes and Best Practices

• Understand How UNIQUE Handles NULL: In MySQL, NULL != NULL, so a nullable UNIQUE column permits multiple NULL rows while rejecting duplicate non-null values. If you want at most one NULL, store a sentinel or normalize via a generated column.
• Find Existing Duplicates Before Adding UNIQUE: Run SELECT email, COUNT(*) FROM demo_customers GROUP BY email HAVING COUNT(*) > 1; and clean up duplicates before running ALTER TABLE ... ADD UNIQUE.
• Know That UNIQUE Creates an Index Automatically: You do not need to create a separate INDEX on a UNIQUE column—MySQL already builds a unique B-tree index for it.

Frequently Asked Questions

What is the difference between PRIMARY KEY and UNIQUE?

A table can have only one PRIMARY KEY (which forbids NULL and clusters InnoDB data), whereas it can have many UNIQUE constraints (which permit NULL).

How do I drop a UNIQUE constraint in MySQL?

Drop its underlying index name using ALTER TABLE demo_customers DROP INDEX uq_demo_customers_email;.

Does UNIQUE depend on case sensitivity?

Yes, under standard _ci collations (utf8mb4_0900_ai_ci), '[email protected]' and '[email protected]' collide as duplicates.