Sybase DC38133-01-0902-01 Primary key pool, Columns of this table have the following meanings 175

Chapter 8. SQL Remote Design for Adaptive Server Enterprise

Ensuring unique primary keys

Users at physically distinct sites can each INSERT new rows to a table, so there is an obvious problem ensuring that primary key values are kept unique.

If two users INSERT a row using the same primary key values, the second INSERT to reach a given database in the replication system will fail. 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 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 a general, economical and reliable method that uses a pool of primary key values for each site in the installation.

Overview of primary key The primary key pool is a table that holds a set of primary key values for

poolseach database in the SQL Remote installation. Each remote user receives their own set of primary key values. When a remote user inserts a new row into a table, they use a stored procedure to select a valid primary key from the pool. The pool is maintained by periodically running a procedure at the consolidated database that replenishes the supply.

The method is described using a simple example database consisting of sales representatives and their customers. The tables are much simpler than you would use in a real database; this allows us to focus just on those issues important for replication.

The primary key pool

The pool of primary keys is held in a separate table. The following CREATE

TABLE statement creates a primary key pool table:

CREATE TABLE KeyPool ( table_name VARCHAR(40) NOT NULL, value INTEGER NOT NULL, location VARCHAR(6) NOT NULL, PRIMARY KEY (table_name, value),

)

go

The columns of this table have the following meanings:

175