Generate integer IDs (unique, usable for primary key)

How to generate integer IDs itself is easy: Use an Integer (Autoinc) field type and it'll automatically get an incrementing value. The default of this type of field is like with SQL Server Identity or MS Access Autonumber or sequences in other databases: Start value 1, increment 1, so you get values 1,2,3....

There are some rules for such fields: They are read only, they are generated when inserting into the DBF file and then only readable.

And that has some consequences in working with autoinc fields: You can't append into them, eg from CSV or other DBFs. You can suppress error messages, when setting SET AUTOINCERROR OFF, but that'll just suppress the error, it's not like SET IDENTTIY OFF enabling to write into the autoinc field.

Another common problem with autoinc fields is with buffered updatable views. Records you insert buffered get an autoinc value and the autoinc counter in the DBF header file is incremented right then and available to any concurrent clients also adding buffered data to the DBF, but it has it's problems with generating IDs and using them as foreign keys in buffered modes and then reverting.

For this and other reasons it's still preferable to have a writable ID/primary key field in your tables. You can do this and still make use of the reliability of autoinc, by using a stored procedure making use of a single autoinc field in a single database system table just being used as source of new ids globally for all tables. That means you have the additional benefit of any ID integer value being unique in the database, not only in each single table.

[code VFP]#Define ccDesiredDirectoryOfDBC "C:\AppData\Yourcompany\GAI\" && whatever desired target folder for your database, it must exist, so create it, if it doesn't
Local lcStoredProcs, lcTempfile

* To apply to an existing database open it here, instead of creating a new one:
Cd (ccDesiredDirectoryOfDBC)
Create Database 'GlobalAutoinc.DBC'

Set Database To GlobalAutoinc
Create Table 'S_Auto.DBF' (iInc I Not Null Autoinc Nextvalue 1 Step 1) && the only autoinc field ever needed

* Sample tables for usage of the S_Auto autoinc field via stored proc
Create Table 'SampleTable1.DBF' (Id I Not Null Default gai(), cText C(10))
Alter Table 'SampleTable1' Add Primary Key Id Tag Id Collate 'MACHINE'

Create Table 'SampleTable2.DBF' (Id I Not Null Default gai(), cText C(10))
Alter Table 'SampleTable2' Add Primary Key Id Tag Id Collate 'MACHINE'

Close Tables All

* definition of the stored proc "gai()"
TEXT To lcStoredProcs NoShow
Function gai() && gai = global auto inc
Local lnAutoinc

* Adapt this to your database name: GlobalAutoinc!S_Auto=> YourDbc!S_Auto
Use GlobalAutoinc!S_Auto In Select("S_Auto") Again Alias S_Auto
Begin Transaction
Append Blank In S_Auto
lnAutoinc = S_Auto.iInc
RollBack && never keep records in the S_Auto.dbf, just use the autoinc counter for the iInc field in the S_Auto.dbf header.
Use In Select("S_Auto")

Return m.lnAutoinc
EndFunc
ENDTEXT

lcTempfile = Addbs(Getenv("TEMP"))+Sys(2015)
Strtofile(lcStoredProcs,lcTempfile,.F.)
Append Procedures From (lcTempfile)
Erase (lcTempfile)

Compile Database GlobalAutoinc

Insert Into SampleTable1 (cText) Values ("hello") && will get ID=1
Insert Into SampleTable2 (cText) Values ("world!") && will get ID=2, not 1 as you would with a separate autoinc integer for each table.

*From now on, wherever you insert/append new data it'll get the next autoinc value.
[/code]

As the sample tables demonstrate, the ID counter is used by both tables and wherever you add the next record in one of them, the ID will be 3.