SQL Unique

SQL UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different. This prevents duplicate entries and helps keep your data clean.

UNIQUE in CREATE TABLE

Create a table where email must be unique:

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE
);

UNIQUE on Multiple Columns

You can enforce uniqueness across a combination of columns.

CREATE TABLE subscriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  topic_id INT NOT NULL,
  UNIQUE (user_id, topic_id)
);

Add UNIQUE with ALTER TABLE

Add a unique constraint to an existing table:

ALTER TABLE customers
ADD UNIQUE (email);

Named UNIQUE Constraint

Giving the constraint a name makes it easier to drop later:

ALTER TABLE customers
ADD CONSTRAINT uq_customers_email UNIQUE (email);

Drop UNIQUE Constraint (MySQL / MariaDB)

In MySQL/MariaDB, UNIQUE is implemented as a unique index. You drop it with DROP INDEX.

ALTER TABLE customers
DROP INDEX uq_customers_email;

UNIQUE and NULL Values

Many database systems allow multiple NULL values in a UNIQUE column because NULL means “unknown”. Behavior can vary by database and collation.

Common Mistakes

• Adding UNIQUE to a column that already contains duplicates
• Forgetting to name constraints, making them harder to remove
• Assuming UNIQUE also prevents NULL values (use NOT NULL if required)

Best Practice

Use UNIQUE for identifiers like emails, usernames, or natural keys. Combine UNIQUE with NOT NULL when the value is required.

Next Step

Continue with SQL PRIMARY KEY to learn how to uniquely identify each row in a table.