How Unique Indexes Affect Auto-Increment and How to Handle Duplicate Keys in MySQL

Creating a Table with a Unique Index

The example creates a sc table for student information, defining columns such as id, name, class, score, timestamps, and user IDs. A unique index is defined on the name column, ensuring that each student name can appear only once.

DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `class` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_user_id` bigint(11) DEFAULT NULL COMMENT '创建人id',
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
  `modify_user_id` bigint(11) DEFAULT NULL COMMENT '最后修改人id',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='学生信息表';

This unique index means that two rows cannot have the same name value.

Adding and Dropping Unique Indexes

To add a unique index after the table is created:

ALTER TABLE sc ADD UNIQUE (name);
ALTER TABLE sc ADD UNIQUE KEY `name_score` (name, score);

To drop the unique index:

ALTER TABLE sc DROP INDEX `name`;

Effect of Unique Index on Auto‑Increment

Insert several distinct rows:

INSERT INTO sc (name, class, score) VALUES ('吕布','一年二班',67);
INSERT INTO sc (name, class, score) VALUES ('赵云','一年二班',90);
INSERT INTO sc (name, class, score) VALUES ('典韦','一年二班',89);
INSERT INTO sc (name, class, score) VALUES ('关羽','一年二班',70);

After these inserts, AUTO_INCREMENT becomes 5. Attempting to insert another row with a duplicate name triggers an error and increments AUTO_INCREMENT to 6, even though the insert fails:

INSERT INTO sc (name, class, score) VALUES ('吕布','二年二班',77);
-- Error 1062: Duplicate entry '吕布' for key 'name'
-- Execution time: 0.01s

Besides raising an error on duplicate data, a UNIQUE index also causes the AUTO_INCREMENT counter to advance.

Unique vs. Primary Key

In MySQL, a primary key is essentially a UNIQUE index with a NOT NULL constraint. Key differences include:

Unique columns may contain NULL values; primary key columns cannot.

Multiple columns can form a unique constraint, but only one primary key is allowed per table.

Creating a unique constraint automatically creates a non‑clustered unique index (unless a clustered index is explicitly specified).

Primary keys are primarily used for foreign‑key references.

A table can have many unique keys but only one primary key.

Strategies to Avoid Duplicate‑Key Errors

INSERT IGNORE

INSERT IGNORE

skips rows that would violate a unique or primary‑key constraint, leaving existing rows unchanged while still advancing the auto‑increment counter.

INSERT IGNORE INTO sc (name, class, score) VALUES ('吕布','二年二班',77);

REPLACE INTO

REPLACE INTO

attempts to insert a row; if a duplicate key is found, it first deletes the existing row and then inserts the new one. This requires both DELETE and INSERT privileges, and the id will be regenerated.

REPLACE INTO sc (name, class, score) VALUES ('吕布','二年二班',77);

INSERT … ON DUPLICATE KEY UPDATE

This statement inserts a new row, but if a duplicate key is detected, it performs an UPDATE on the existing row. The affected‑rows count indicates the action taken (1 for insert, 2 for update, 0 if values are unchanged).

INSERT INTO sc (name, class, score) VALUES ('关羽','二年二班',80) ON DUPLICATE KEY UPDATE score=100;
-- Affected rows: 2

In the example, only the score column changes; the id remains the same, but the auto‑increment counter still increments.

Potential Deadlock with ON DUPLICATE KEY

When InnoDB processes INSERT … ON DUPLICATE KEY UPDATE, it first acquires a shared lock (S) on the existing row, then, after returning the row, acquires an exclusive lock (X) to perform the update. Concurrent transactions executing the same statement can cause a deadlock.

Mitigation suggestions:

Avoid using the statement on tables that already have multiple unique keys.

Do not use it when concurrent transactions may insert identical data.

Conclusion

All three methods— INSERT IGNORE, REPLACE INTO, and INSERT … ON DUPLICATE KEY UPDATE —prevent insertion failures caused by duplicate primary or unique keys. INSERT IGNORE silently skips duplicate rows, inserting only new ones. REPLACE INTO deletes the existing row before inserting a new one, which changes the auto‑increment value. INSERT … ON DUPLICATE KEY UPDATE updates the existing row without deleting it; the auto‑increment counter still advances.