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

To avoid primary key errors, you must ensure that when a database inserts a row, its primary key is guaranteed to be unique across all databases in the system. There are several techniques for achieving this goal, including:

Using the default GLOBAL AUTOINCREMENT feature of SQL Anywhere databases.
Using a primary key pool to maintain a list of unused, unique primary key values at each site.

These techniques can be used either separately or together to avoid duplicate primary keys.

Duplicate primary key errors

Primary key values must be unique

See also