How Primary Keys are created

By default wwBusiness works with integer PKs and uses a lookup table to generate new ids as objects are created. The default process works like this:

When you call the New() method:

  • New() creates a new blank record and issues a SCATTER NAME on it
  • New() calls CreateNewId() to generate a new PK and assigns it to the PK field
  • CreateNewId() internally calls a VFP Function or SQL Server Stored Procedure to generate a new Integer PK
  • You can override CreateNewId() to return a custom value with your own logic

How the default New Id Creation behavior works

The key here is the CreateNewId() method which generates the new Integer Pk. This default behavior uses a lookup table which contains a key for the table name and an integer value that is incremented. When a new Id is requested the single record is briefly locked and the value incremented by one and returned.

FoxPro New Id Creation

In FoxPro this behavior is handled by FoxPro code inside of CreateNewId() using the table defined in the cIDTable property.

Sql Server New Id Creation

For SQL Server a stored procedure sp_ww_NewId is used and it relies on a table called wws_id to hold the tables:

USE [WebConnection_Admin]
GO
CREATE PROCEDURE [dbo].[sp_ww_NewID]
  @cName char(30),
  @nRetval int OUTPUT
AS
UPDATE wws_Id 
  SET id = id + 1,
      @nRetval = id + 1
WHERE TableName = @cName

The table and stored procedure can be automatically generated from the Console using the Create Sql Server Tables.

The wws_id table is very simple and looks like this:

CREATE TABLE [dbo].[wws_id](
   [tablename] [varchar](50) NOT NULL,
   [id] [int] NOT NULL)

Each table that you want to create a PK for should have an entry in tablename and id should start at 0.


© West Wind Technologies, 1996-2018 • Updated: 11/09/18
Comment or report problem with topic