SQL Remote User's Guide
SQL Remote Design for Adaptive Server Anywhere
Primary key values must be unique. When all users are connected to the same database, there is no problem keeping unique values. If a user tries to re-use a value, 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 databases, insert a row using the same primary key value. Each of their statements succeeds because the value is unique in each database.
However, problems arise in a replication system when two users, connected to separate databases, INSERT a row using the same primary key value. The second INSERT to reach a given database in the replication system fails. As SQL Remote is a replication system for occasionally connected users, there can be no locking mechanism across all databases in the installation. It is necessary to design your SQL Remote installation so that primary key duplication errors do not occur.
For primary key errors to be designed out of SQL Remote installations, the primary keys of tables that may be modified at more than one site must be guaranteed unique. There are several ways of achieving this goal. This chapter describes two general, economical, and reliable methods.
Using the default global autoincrement feature of Adaptive Server Anywhere.
Using the primary key pools to maintain a list of unused, unique primary key values at each site.
You can use these techniques either separately or together to avoid duplicate values.
Using global autoincrement default column values
Using primary key pools
The primary key pool table
Replicating the primary key pool
Filling and replenishing the key pool
Adding new customers
Primary key pool summary