The primary key pool table
Thepool of primary keys is held in a separate table. Thefollowing CREATE
TABLEstatement creates a primary key pool table:
CREATE TABLE KeyPool (
table_name VARCHAR(40) NOT NULL,
value INTEGER NOT NULL,
location CHAR(12) NOT NULL,
PRIMARY KEY (table_name, value),
);
Thecolumns 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 keypool
andthis column is redundant. Itis included to show a general
solution.
value Holdsa list ofprimary key values. Each value is unique for
eachtable listed in table_name.
location An identifier for the recipient. In some setups, this could be
thesame as the rep_key value of the SalesRep table. In other
setups,there will be users other than sales representatives and
thetwo identifiers should be distinct.
Forperformance reasons, you may wish to create an index on the table:
CREATE INDEX KeyPoolLocation
ON KeyPool (table_name, location, value);
Replicating the primary key pool
Youcan either incorporate the key pool into an existing publication, or share
itas a separate publication. In this example, we create a separate publication
forthe primary key pool.
❖Toreplicate the primary key pool (SQL)
1. Create a publication for the primary key pool data.
CREATE PUBLICATION KeyPoolData (
TABLE KeyPool SUBSCRIBE BY location
);
134