Most popular RDBMS propose auto-increment field for primary key for table, specially for normalized databases. VFP don't have such feature. I suppose following ideas will be useful for all VFP programmers that deal with databases.
Use default value for key field to fill it automatically by new key value each time new record added. As the default expression use function that returns this key. For example:
GetNextID - function in database. In project you can use it either as separate .PRG file or as function inside of your main program file or procedure file, but it is not recommended to duplicate it in two places, because you may call database trigger functions from VFP code when that database is current. This function should return new ID key value.
Following is sample of such function. This way is the most flexible way with great speed, but it require a lot of programming and maintenance. You can calculate key using special table (IDS), where you store all tables and their latest ID KEY numbers. Following is sample of implementation of such function:
PROCEDURE GetNextID LPARAMETER tcTable IF !USED("ids") USE MyDatabase!ids IN 0 order table ELSE set orde to table in ids ENDIF IF !SEEK(UPPER(tcTable), "ids") INSERT INTO ids VALUES(UPPER(tcTable),0) ENDIF DO WHILE .T. IF RLOCK("ids") REPLACE last WITH last + 1 IN ids UNLOCK IN ids EXIT ENDIF ENDDO RETURN ids.last
The structure of IDS table is following:
Field name | type ___________|__________________________ table......|character (25) last.......|integer
table - table name in which last key field value maintained last - last key value
You require also to add indexes for ids table: index name | expression _____________________________ Table......| "UPPER(table)"
You may do this using command INDEX ON UPPER(table) TAG Table Additive
This function works quickly because uses small table (ids), record locking in it affects only IDS table and conflicts will occur only in rare case (when 2 users in network will try to add the same record to same table simultaneously). Conflicts resolved by record locking. The disadvantage is that you need to make maintenance program for IDS table to assure that all ID values in that table are latest. This is needed only when you copy separate table data from one database to another, that is very rare.
Alternatively, when you're sure that some table is small and accessed by users rarely, you can use direct calculation method based on indexes, or even SELECT with MAX aggregate function. Finally, you may use both methods, so you will have only large and oftenly accessed tables in IDS table for less maintenance, when all other tables will use direct calculations.
Little note about views. If you do not want to requery view each time after new record saved by view that based on table with auto-inrement key fiel, do following: 1. Define default value for key field in view by the same way as in table. 2. Make key field updatable. This is needed, otherwise your key value will be ignored and table default value will be used, that means GetNextID funtion will be called twise and different values will be in table and view, that may cause problems when adding new records to child tables as well. 3. DO NOT use SetFldState() function to mark key field in view by '4' - new and changed. SetFldState is buggy. To force VFP to overwrite field in table by View's key value anyway, use 'Replace KeyField with KeyField' command.