User's Manual

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
pools
The primary key pool is a table that holds a set of primary key values for
each 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