UNIQUE Constraint
In this page:
column_name datatype UNIQUEUnderstanding UNIQUE
UNIQUE guarantees no two rows can share the same value in that column, which is the standard way to enforce things like one-email-per-account without relying on application-level checks alone.
Example: Understanding UNIQUE
CREATE TABLE users (email VARCHAR(100) UNIQUE);Duplicate Value Errors
Attempting to insert a value that already exists in a UNIQUE column causes MySQL to reject the entire insert and return a duplicate-key error rather than silently allowing the collision.
Example: Duplicate Value Errors
CREATE TABLE users (email VARCHAR(100) UNIQUE);
INSERT INTO users VALUES ('[email protected]');
INSERT INTO users VALUES ('[email protected]'); -- duplicate-key errorMultiple Unique Columns
A table can carry several independent UNIQUE columns at once, and you can also combine multiple columns into a single composite UNIQUE constraint that only fires when the *combination* repeats.
Example: Multiple Unique Columns
CREATE TABLE registrations (
event_id INT,
user_id INT,
UNIQUE (event_id, user_id)
);Adding UNIQUE to Existing Tables
Adding UNIQUE to an already-populated column via ALTER TABLE will fail immediately if any duplicate values already exist in the data, so cleanup usually has to happen first.
Example: Adding UNIQUE to Existing Tables
ALTER TABLE users ADD UNIQUE (email);Dropping UNIQUE Constraints
A UNIQUE constraint is implemented as an index behind the scenes, so removing it means dropping that index specifically — MySQL doesn't offer a separate unconstrain command.
Example: Dropping UNIQUE Constraints
ALTER TABLE users DROP INDEX email;- Assuming
UNIQUEprevents multipleNULLvalues, when MySQL allows severalNULLvalues in a unique column. - Adding
UNIQUEto a column that already has duplicate values, which fails. - Expecting two separate
UNIQUEcolumns to be unique together, when a combined rule needsUNIQUE (a, b).
Chapter Quiz — Complete all 8 topics to unlock
0/8 topics done
Complete these topics first: