Duplicate primary key errors

When all users are connected to the same database, there is no problem ensuring that each INSERT statement uses a unique primary key. If a user tries to re-use a primary key, the INSERT statement fails.

The situation is different in a replication system because users are connected to many databases. A potential problem arises when two users, connected to different remote databases, insert a row using the same primary key value. Each of their statements succeeds because the primary key value is unique on each remote database.

However, when these two users replicate their databases with the same consolidated database, a problem arises. The first database to replicate with the consolidated database succeeds. However, the second insert to reach a given database in the replication system fails.

 Primary key values must be unique
 See also

Global autoincrement columns
Using primary key pools