Using MySQL UNIQUE Index To Prevent Duplicates

A UNIQUE index in MySQL ensures that all values in a column or combination of columns are distinct across rows. It helps prevent duplicate entries in a table.

1. Creating a UNIQUE Index

Syntax

CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);

✅ Prevents duplicate values in the specified columns.

2. Adding a UNIQUE Constraint When Creating a Table

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    username VARCHAR(100) NOT NULL,
    UNIQUE (email),
    UNIQUE (username)
);

✅ The email and username columns cannot have duplicate values.

3. Adding a UNIQUE Index to an Existing Table

ALTER TABLE users ADD UNIQUE (email);

✅ Prevents duplicate email addresses from being inserted.

4. Handling Duplicate Entry Errors

If you try to insert a duplicate value, MySQL will return an error:

INSERT INTO users (email, username) VALUES ('test@example.com', 'testuser');
INSERT INTO users (email, username) VALUES ('test@example.com', 'newuser'); -- ❌ ERROR

🔴 Error: Duplicate entry 'test@example.com' for key 'users.email'

5. Using IGNORE to Avoid Errors

If you don't want an error but want MySQL to skip duplicate entries, use INSERT IGNORE:

INSERT IGNORE INTO users (email, username) VALUES ('test@example.com', 'newuser');

✅ The query does not insert the row but also does not throw an error.

6. Using REPLACE to Handle Duplicates

The REPLACE statement deletes the existing row and inserts a new one:

REPLACE INTO users (email, username) VALUES ('test@example.com', 'newuser');

✅ Deletes the old row and inserts the new one.

7. Creating a UNIQUE Index on Multiple Columns

To enforce uniqueness across multiple columns:

CREATE UNIQUE INDEX unique_name ON users (email, username);

✅ Now, email and username together must be unique.

8. Checking Existing UNIQUE Indexes

SHOW INDEX FROM users WHERE Non_unique = 0;

✅ Lists all UNIQUE indexes in the users table.

9. Dropping a UNIQUE Index

ALTER TABLE users DROP INDEX email;

✅ Removes the UNIQUE constraint on email.

10. Conclusion

The UNIQUE index prevents duplicate values.
It can be applied to single or multiple columns.
Use INSERT IGNORE or REPLACE to handle duplicates gracefully.
Check existing indexes with SHOW INDEX.

🚀 Use UNIQUE indexes to enforce data integrity in MySQL!

Souy Soeng

Using MySQL UNIQUE Index To Prevent Duplicates