Sybase DC38133-01-0902-01 manual Ensuring unique primary keys

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