Contents Index Ensuring unique primary keys Replicating the primary key pool

SQL Remote User's Guide
  SQL Remote Design for Adaptive Server Enterprise
    Ensuring unique primary keys

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:

Column Description
table_name Holds the names of tables for which primary key pools must be maintained. In our simple example, if new sales representatives were to be added only at the consolidated database, only the Customer table needs a primary key pool and this column is redundant. It is included to show a general solution.
value Holds a list of primary key values. Each value is unique for each table listed in table_name.
location In some setups, this could be the same as the rep_key value of the SalesRep table. In other setups, there will be users other than sales representatives and the two identifiers should be distinct.

For performance reasons, you may wish to create an index on the table:

CREATE INDEX KeyPoolLocation
ON KeyPool (table_name, location, value)
go

Contents Index Ensuring unique primary keys Replicating the primary key pool