Autoincrementing Field Values in Tables

Article
11/14/2006

You can specify automatically incrementing field values for both database container (DBC) tables and free tables. A table can contain multiple fields that use autoincrementing, which Visual FoxPro supports only for fields of Integer data type but not for local, remote, or offline views.

You can turn on autoincrementing for a field in a database container (DBC) or free table by using Visual FoxPro commands or the Table Designer. You can retrieve the autoincrement settings for a field using the DISPLAY STRUCTURE and LIST commands.

A field that uses autoincrementing becomes read-only and cannot be changed with an insert, update, or replace operation. Attempting to update such a field generates an error message unless you set the AutoIncError cursor property to False (.F.) or turn the error message off by using the SET AUTOINCERROR command. For more information, see the AutoIncError property in CURSORSETPROP( ) Function and SET AUTOINCERROR Command.

When you change a table that does not contain an autoincrementing field to include one, either by using ALTER TABLE or the Table Designer, autoincrementing begins with the next added row. Previous rows in the table are not updated with autoincrementing values starting with the first record. You need to make sure that no conflicts occur as a result.

When you turn on autoincrementing for any field in a table, Visual FoxPro sets byte 0 to 0x31 for the file type "Visual FoxPro, Autoincrement enabled" in the table (.dbf) header record structure. Visual FoxPro sets byte 18, bytes 19 to 22, and byte 23 in the field subrecords structure to the following values, respectively:

0x0C for the autoincrementing column
Next autoincrement value
Step autoincrement value

For more information, see Table File Structure.

Issues Concerning Using Automatically Incrementing Field Values

Tables containing automatically incrementing field values append table-buffered records approximately 35% slower than tables without automatically incrementing field values, which might affect performance. When using table buffering, the table header is locked when the record is appended.

Visual FoxPro does not manage gaps in generated sequences. Gaps can be caused by reverting an appended or inserted record or by failing to update the base table and so on. In all cases, the unused value is lost, and the next generated value remains the same as if the append or insert operation succeeded.

Older versions of Visual FoxPro cannot recognize tables that use autoincrementing. If you remove autoincrementing, the current state containing the last incrementing and incremental values is cleared from the .dbf field subrecord and discarded. The .dbf type is restored to the current Visual FoxPro type value. The autoincrement values previously stored in each record remain.

Autoincrement Using Visual FoxPro Commands

You can use the AUTOINC clause in the CREATE TABLE and ALTER TABLE SQL commands to turn on autoincrementing for fields. For more information about using the AUTOINC clause, see CREATE TABLE - SQL Command and ALTER TABLE - SQL Command.

Autoincrement Using the Table Designer

The Fields tab in the Table Designer includes a data type called Integer (AutoInc) in the Type drop-down list for a particular field. When you select the Integer (AutoInc) data type for a particular field, Visual FoxPro enables autoincrementing for that field.

Note When you select the Integer (AutoInc) data type for a newly created field, the default value field in the Fields tab becomes unavailable. If you want to specify a default value, you must first change the Integer (AutoInc) data type.

When you select the Integer (AutoInc) data type for an existing field with a default value, the default value field becomes unavailable. However, Visual FoxPro neither discards nor uses the value. The default value field becomes available again when you change the data type from Integer (AutoInc). Additionally, expressions that you type in the default value field remain when you change the field type to or from Integer (AutoInc). You can still use code such as DBGETPROP( ) that retrieves the default value for a field.

The Fields tab includes the AutoIncrement group box, which contains the Next Value and Step spin boxes. You can use these spin boxes to set the start and increment values for the field value. For more information about the Next Value and Step values and setting autoincrementing properties using the Table Designer, see Fields Tab, Table Designer and Setting Autoincrementing Field Values.

Local Views

Views do not "inherit" autoincrementing behavior of the base table, and the fields in the view that represent autoincrementing fields in the base table are read/write. Autoincrementing occurs in the base table when the row or rows are updated in the base table. You must use the REQUERY( ) function if you want to refresh the view with the autoincrement value previously generated.

Buffered Tables

Visual FoxPro does not perform any autoincrementing management relative to buffered tables. All appended and inserted records have autoincrementing values generated regardless of whether table or row buffering is active. In the case of buffering, when the TABLEUDPATE( ) function is called, the base table is updated with the previously generated value. If the update does not occur, for example, the TABLEREVERT( ) function is called, any autoincrementing values that are generated are discarded, thus resulting in gaps in the sequence.

Record Locking

When you turn on autoincrementing for a field, the start and incremental values, Next Value and Step, are stored in the table (.dbf) header in the unused or reserved portion of the field subrecord for the specified field. Next Value is stored as a 4-byte integer. The Step value is stored as a 1-byte integer with a maximum value of 255. The value actually used to increment the field value is the sum of the value stored in the .dbf header and the incremental value. The operation sequence occurs as follows:

Perform an insert or append operation.
Lock header.
Increase the currently stored increment value by the Step value and apply to the field.
Store the new value, which is the value of the newly added record, in the .dbf file header.
Unlock header when insert or append operation completes. The header contains the last incremented value.