KB006: Primary Keys

Topic: Visual FoxPro

Last updated: May 10, 2001

Primary keys must be unique for each record in the table. This means that you can't repeat the same value anywhere in the table. Most developers have problems when trying to reuse the primary key. The answer is...don't. There really is no reason to reuse the primary key value. Ideally, the primary key is system generated, meaning the user doesn't enter it. You can use an autoincrementing key. This Knowledgebase article describes how to do it.

First, create a table called KEYS.DBF with two fields, a 20 character field named Table and NextID of type Integer.

Next, create the primary key field in your table. Tests have shown that Integers are the fastest. I name my primary key fields TableID. So, for example, if the table is named CLIENT.DBF, the primary key field will be called ClientID. I also use the same field name as foreign keys in related tables. This way, whenever I see the field ClientID, I know the table it comes from and what it means.

Third, set the Default value of the primary key field. You should call a function in the Stored Procedures. For example, GetKey("Client") will provide the default value for ClientID when you add a new record to the Client table.

Put the GetKey function in the Stored Procedures so it can be found. The following code can be cut and pasted into the VFP editor.

FUNCTION GetKey
LPARAMETERS tcTable

LOCAL lnArea, llOpened, liRetVal

* Save current work area
lnArea = SELECT()
llOpened = .F.

IF !USED("Keys")
  USE Keys IN 0 SHARED
  llOpened = .T.
ENDIF
SELECT Keys
SET ORDER TO TAG Table

* FLOCK seems to work a bit better than RLOCK.
* I've never had a problem with it.
DO WHILE !FLOCK()
ENDDO

SEEK UPPER(tcTable)
IF FOUND()
  liRetVal = NextId
  REPLACE NextId WITH NextId + 1
ELSE
  INSERT INTO Keys (Table, NextId) VALUES (tcTable, 2)
  liRetVal = 1
ENDIF

FLUSH
UNLOCK

IF llOpened
  USE IN Keys
ENDIF

SELECT (lnArea)
RETURN liRetVal


                                    Copyright © 2001-2005, Craig Berntson.  All Rights Reserved.