UNIQUE Index
In this page:
CREATE UNIQUE INDEX index_name
ON table_name (column_name);What is a Unique Index?
A UNIQUE index enforces that every value stored in the indexed column is different from every other row's value, rejecting any insert or update that would create a duplicate.
Example: What is a Unique Index?
CREATE TABLE users (id INT, email TEXT UNIQUE);
INSERT INTO users VALUES (1, '[email protected]');
-- Fails: duplicate email
INSERT INTO users VALUES (2, '[email protected]');Creating UNIQUE Index during Table Creation
Defining a unique index at table-creation time bakes the constraint in from the very first row, which is the cleanest approach when you already know a column — like an email address — must never repeat.
Example: Creating UNIQUE Index during Table Creation
CREATE TABLE users (id INT, email TEXT, UNIQUE (email));
INSERT INTO users VALUES (1, '[email protected]');Adding UNIQUE Index to Existing Tables
For a table that already exists and already has data, ALTER TABLE lets you add a unique index after the fact, though MySQL will refuse if the existing data already contains duplicate values.
Example: Adding UNIQUE Index to Existing Tables
CREATE TABLE users (id INT, email TEXT);
INSERT INTO users VALUES (1, '[email protected]');
ALTER TABLE users ADD UNIQUE (email);Composite Unique Index
A composite unique index applies uniqueness to the combination of several columns together rather than each column alone — individual columns can repeat, but no two rows may share the exact same combination.
Example: Composite Unique Index
CREATE TABLE enrollments (student_id INT, course_id INT, UNIQUE (student_id, course_id));
INSERT INTO enrollments VALUES (1, 101);
-- Allowed: same student, different course
INSERT INTO enrollments VALUES (1, 102);Dropping a Unique Index
Dropping a unique index removes the uniqueness enforcement along with the performance benefit, which means duplicate values become allowed in that column again going forward.
Example: Dropping a Unique Index
CREATE TABLE users (id INT, email TEXT, UNIQUE KEY uq_email (email));
ALTER TABLE users DROP INDEX uq_email;- Creating a unique index on a column that already has duplicates, which fails.
- Assuming a unique index blocks multiple
NULLvalues, when MySQL allows severalNULLs. - Expecting two separate unique indexes to make a pair unique, when a pair needs one composite
UNIQUE (a, b).
- A view is a saved query that you can create, update, and drop.
- Indexes speed up data lookups and can be created and dropped.
- A
UNIQUEindex also prevents duplicate values.
Chapter Quiz — Complete all 5 topics to unlock
0/5 topics done
Complete these topics first: