Using the primary keys from the key pool

 Use the primary keys (SQL)

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.



    CREATE PROCEDURE NewKey(
          IN @table_name VARCHAR(40),
          OUT @value INTEGER )
    BEGIN
       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;
       ELSE
       // 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;
    END;

    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 ) )
    BEGIN
       DECLARE new_cust_key INTEGER ;
       CALL NewKey( 'Customers', new_cust_key );
       INSERT
       INTO Customers (
          cust_key,
          name,
          location
          )
       VALUES (
          'Customers ' ||
          CONVERT (CHAR(3), new_cust_key),
          customer_name,
          CURRENT PUBLISHER
          );
    END

    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.