MySQL — Unique

Basic Unique Constraint

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    username VARCHAR(50) UNIQUE
);

Named Unique Constraint

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255),
    CONSTRAINT uq_email UNIQUE (email)
);

Composite Unique

CREATE TABLE enrollments (
    student_id INT,
    semester VARCHAR(20),
    CONSTRAINT uq_enrollment UNIQUE (student_id, semester)
);

Adding Unique After Creation

ALTER TABLE users ADD UNIQUE (email);
-- or with a name
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);

Dropping Unique

ALTER TABLE users DROP INDEX uq_email;

UNIQUE vs PRIMARY KEY

Feature	UNIQUE	PRIMARY KEY
NULLs allowed	Yes (one per column)	No
Count per table	Multiple	One
Auto-indexed	Yes	Yes

Viewing Unique Constraints

SHOW INDEX FROM users WHERE Non_unique = 0;

Mini Practice

Write SQL code that:

1. Creates a table with a UNIQUE constraint
2. Creates a composite UNIQUE constraint
3. Adds UNIQUE to an existing column
4. Drops a UNIQUE constraint

Up Next

Continue with Check — validating column values with conditions.