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

SQL Anywhere 12.0.0 (Français) » SQL Remote » Creating SQL Remote systems » Duplicate primary key errors » Using primary key pools


Use the primary keys from the key pool

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.

 ♦  To use the primary keys (SQL)
  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.