Primary key pools

A primary key pool is a table that contains a set of primary key values for each database in a SQL Remote system. A master primary key pool table is created and stored on the consolidated database. Remote users subscribe to the consolidated database primary key pool table to receive 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 their pool. The pool is maintained by periodically running a procedure on the consolidated database that replenishes the supply.

The primary key pool technique requires the following components:

Primary key pool table On the consolidated database, you need a table to hold valid primary key values for each database in the system.
Replenishment procedure On the consolidated database, you need a stored procedure to keep the key pool table filled.
Sharing of key pools Each remote database in the system must subscribe to its own set of valid values from the consolidated database key pool table.
Data entry procedures On the remote databases, new rows are entered using a stored procedure that picks the next valid primary key value from the pool and then deletes that value from the key pool.

Primary key pools

See also