INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Databases and tables

How to organize auto-increment key field in VFP? by TomasDill
Posted: 18 Oct 00

  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("companies")

  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.

Back to Microsoft: Visual FoxPro FAQ Index
Back to Microsoft: Visual FoxPro Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close