PRIMARY KEY and UNIQUE Index Constraints and Their Strange Behaviors

The difference between MySQL's primary key constraint and unique index constraint.

When Are Constraint Checks Triggered

MySQL checks constraints during two operations:

• insert
• update

If a constraint violation occurs, the behavior depends on the storage engine:

• If the storage engine supports transactions (like InnoDB), the SQL statement will automatically roll back.
• If the storage engine does not support transactions (like MyISAM), the SQL execution will be interrupted mid-way, potentially leaving the dataset in an unexpected partial state.

Example with InnoDB

create table t1(id int(10) primary key)engine=innodb;
insert into t1 values(1);
insert into t1 values(1);

The second insert violates the primary key constraint, and InnoDB will roll back that SQL statement. Can check errors with:

Example with MyISAM(The Tricky Part)

create table t2(id int(10) unique)engine=MyISAM;
insert into t2 values(1);
insert into t2 values(5);
insert into t2 values(6);
insert into t2 values(10);

update t2 set id=id+1;

What result set do you get after this update? It's 2,5,6,10. Why?

• The first row id=1 gets updated to 2 successfully.
• The second row id=5 attempts to become 6, but since id=6 already exists, this violates the UNIQUE constraint, so SQL execution stops, and this row isn’t modified.
• The third and fourth rows (id=6 and id=10) are not executed at all.

This partial update is some rows are updated, others fail silently.

How to Avoid This Mess?

Use the InnoDB storage engine. InnoDB will roll back the entire update if any constraint violation occurs — no partial updates.

Handling Constraint Violations During INSERT

Can use the insert ... on duplicate key syntax to specify what action to take if a primary key or unique constraint conflict occurs.

Example:

create table t3 (
  id int(10) unique,
  flag char(10) default 'true'
) engine=MyISAM;

insert into t3(id) values(1);
insert into t3(id) values(5);
insert into t3(id) values(6);
insert into t3(id) values(10);

insert into t3(id) values(10) on duplicate key update flag = 'false';

What happens here?

• The attempt to insert id=10 triggers a UNIQUE constraint violation.
• Instead of failing, it executes UPDATE flag='false' on the existing row where id=10.
• Effectively, this is the same as:

update t3 set flag = 'false' where id = 10;

Interestingly, the insert statement returns:

Summary

• Primary key and unique index constraints are checked on insert and update operations.
• InnoDB rolls back the entire SQL on constraint violations.
• MyISAM will partially execute and then stop on constraint violations, which may cause unexpected partial results.
• Can use insert ... on duplicate key update to define custom behavior when constraints are violated.
• Always use show warnings; to debug constraint-related errors.

In high-concurrency, large-scale systems, for the sake of stability (and your sanity), always use InnoDB.