Autoincrementing Field Values in Tables
You can specify that integer fields from database tables and free tables contain values that increment automatically. A table can contain multiple integer fields that contain automatically incrementing values. Autoincrementing is not supported for local, remote, or offline views.
When you add a field with automatically incrementing values to a table that did not contain one, the values begin incrementing automatically with the next row that is added in the table. Previous rows in the table are not updated with automatically incrementing values; therefore, make sure that no conflicts occur as a result.
For more information about how to set automatically incrementing field values, see How to: Set Autoincrementing Field Values.
The following sections contain more information about areas of Visual FoxPro affected by automatically incrementing field values:
Table Structure Changes for Autoincrementing Field Values
Considerations for Autoincrementing Field Values
Local Views and Autoincrementing Field Values
Buffered Tables and Autoincrementing Field Values
Record Locking and Autoincrementing Field Values
Table Structure Changes for Autoincrementing Field Values
When you activate automatically incrementing values 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 value.
Step value.
For more information, see Table File Structure.
Considerations for Autoincrementing Field Values
A field that contains automatically incrementing values 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 cursor AutoIncError property using the CURSORSETPROP( ) function to False (.F.) or turn the error message off by using the SET AUTOINCERROR command. For more information, see CURSORSETPROP( ) Function and SET AUTOINCERROR Command.
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.
Versions prior to Visual FoxPro 8.0 cannot recognize tables that use automatically incrementing field values. If you remove autoincrementing for fields, the current state containing the last incrementing and incremental values is cleared from the table (.dbf) field subrecord and discarded. The table (.dbf) type is restored to the current Visual FoxPro type value. The automatically incrementing values stored previously in each record remain.
Local Views and Autoincrementing Field Values
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 field values occur in the base table when the row or rows are updated in the base table. If you want to refresh the view with the autoincrementing field value previously generated, you must use the REQUERY( ) function. For more information, REQUERY( ) Function.
Buffered Tables and Autoincrementing Field Values
Visual FoxPro does not perform any autoincrementing management relative to buffered tables. All appended and inserted records have autoincrementing field 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, such as when calling the TABLEREVERT( ) function, any autoincrementing field values that are generated are discarded, thus resulting in gaps in the sequence.
Record Locking and Autoincrementing Field Values
When you turn on autoincrementing values 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.