Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Remote » Creating SQL Remote systems » Duplicate primary key errors » Primary key pools


Using the primary keys from the key pool

When remote users add new customers, the primary key comes form the remote user's pool of available primary keys.


The primary key pool table must already have been created.


When a sales representative adds a new customer to the Customers table, the primary key value to be inserted is obtained using a stored procedure. This example uses a stored procedure to supply the primary key value, and a stored procedure to do the insert.

  1. Create a procedure to run on the remote databases to obtain a primary key from the primary key pool table.

    For example, the NewKey procedure supplies an integer value from the key pool and deletes the value from the pool.

          IN @table_name VARCHAR(40),
          OUT @value INTEGER )
       DECLARE NumValues INTEGER;
       SELECT COUNT(*), MIN(value)
       INTO NumValues, @value
             FROM KeyPool
             WHERE table_name = @table_name
             AND location = CURRENT PUBLISHER;
       IF NumValues > 1 THEN
          DELETE FROM KeyPool
          WHERE table_name = @table_name
          AND value = @value;
       // Never take the last value, because
       // ReplenishPool will not work.
       // The key pool should be kept large enough
       // that this never happens.
          SET @value = NULL;
       END IF;

    The NewKey procedure takes advantage of the fact that the Sales Representative identifier is the CURRENT PUBLISHER of the remote database.

  2. Create a procedure that runs on the remote databases to insert a new row in a subscribed table.

    For example, the NewCustomers procedure inserts a new customer into the table, using the value obtained by NewKey to construct the primary key.

    CREATE PROCEDURE NewCustomers(
          IN customer_name CHAR( 40 ) )
       DECLARE new_cust_key INTEGER ;
       CALL NewKey( 'Customers', new_cust_key );
       INTO Customers (
       VALUES (
          'Customers ' ||
          CONVERT (CHAR(3), new_cust_key),

    You can enhance this procedure by testing the new_cust_key value obtained from NewKey to check that it is not NULL, and prevent the insert if it is NULL.


The primary key is set.