User's Manual

Chapter 7. SQL Remote Design for Adaptive Server Anywhere
Ensuring unique primary keys
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.
1. Using the default global autoincrement feature of Adaptive Server
Anywhere.
2. 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
In Adaptive Server Anywhere, you can set the default column value to be
GLOBAL AUTOINCREMENT. You can use this default for any column in
which you want to maintain unique values, but it is particularly useful for
primary keys. This feature is intended to simplify the task of generating
unique values in setups where data is being replicated among multiple
databases, typically by MobiLink synchronization.
When you specify default global autoincrement, the domain of values for
that column is partitioned. Each partition contains the same number of
values. For example, if you set the partition size for an integer column in a
database to 1000, one partition extends from 1001 to 2000, the next from
2001 to 3000, and so on.
129