Generating Surrogate Primary Keys

Tip: This section applies to tables, views and CursorAdapters.

Traditionally, primary keys have been based on fields in a table that are natural data elements related to the entity for which the table contains data. For example, in a table of employee data, an employee number would be a “natural” key that uniquely identifies employees and that could serve as the table’s primary key.

However, a growing number of developers prefer to use generated, or surrogate, primary key values.

Sequential Primary Keys: Normally under this approach, a table's primary key values are generated by sequentially incrementing a numeric value as records are added to the table. The primary key values are stored in a single primary key field in each table record. The last key value generated for a primary key must be maintained in a table that can be accessed by all application users.

Tip – Auto-Incremented Fields: If you are using Visual FoxPro’s auto-incremented field functionality to generate a sequential primary key, see VFP Auto-Incremented Primary Key Fields.

Globally Unique ID (GUID): Alternatively, some developers are beginning to use Globally Unique ID (GUID) values for primary keys. The primary advantage of the GUID is that the generated values are not sequential and, therefore, do not require the last key value to be maintained for access by all application users. In distributed applications, accessing a last key value can be impossible.

Whether you want to generate sequential or GUID primary key values, VPM Enterprise can do it for you automatically through the Default value field property of the primary key field in a table, view or CursorAdapter.

Ø To generate primary key field values for a table, view or CursorAdapter

1.   Add Primary Key Field to Table: If you are working directly with a table, the table must have a primary key field to hold the generated values. If you are working with a view or CursorAdapter, the table on which the view or CursorAdapter is based must have a primary key field to hold the generated values. In either event, if the table does not have a field to hold the generated primary key values, add such a field to the table and to any views or CursorAdapters based on the table.

·       Field Type Recommended: Character type – easiest type to manipulate with VFP functions as needed. GUID must be character.

·       Field Length Recommended: Sequential – 10 characters; GUID – 23 characters (shortest length that uses easily recognizable characters).

2.   Display Field Property Page for Primary Key Field: On the Data Builder List page, select the primary key field in the table, view or CursorAdapter for which you want generated primary key values. Click the Edit page. VPME displays the Field Properties page.

3.   Select Default Value Generate Option: At the bottom of the Field Properties page, you will find an option button group that allows you to choose how Default values should be determined for the field. Two of the options are Generate Sequential or Generate GUID. Chose the generation technique you want for the primary key.

View Tip: When a record is added to a view, if the Default property is set to Off for a field whose source table field is a primary key field in the source table, a check is made to see if the Default property is not set to Off for the source field. If that is the case, the Default value for the view field is produced as defined for the source field. If so, that Default expression is evaluated to produce a default value for the view field. That means that if you set up a table's primary key to be automatically generated, a primary key will be automatically generated for the view's primary key field(s).

Not Just For Primary Keys: While this discussion of the Generate Sequential and Generate GUID functionality has focused on generating values for primary keys, you can use those generation routines for any field. For example, the Generate Sequential routine could be used to generate invoice numbers.

Technical Info

Sequential Values Generation

The code that generates sequential values is in the GenerateKey method of the VPMDataHandler class. Code that determines how the sequential values are incremented is in the GenerateKey_Increment method. The last key value for sequentially generated primary keys is stored in the SDATADDGK.DBF table.

GUID Values Generation

The code that generates GUID values is in the GenerateGUID program. The GenerateGUID program can generate GUIDs of 16, 22, 23, 26, 32, 36, 38, 48 or 128 characters. The number of characters generated is determined by the length of the primary key field passed to the program. For example, if the width of the primary key field is 48 characters, the GUID generated will be 48 characters.

Tip: For more information, read about the generation methods in the “VPMDataHandler Class Methods” section in the Data Handler Class chapter in the VPM Enterprise Technical Reference manual.

More:

VFP Auto-Incremented Primary Key Fields