Article ID: 106708
Article Last Modified on 2/12/2007
APPLIES TO
- Microsoft Visual FoxPro 3.0 Standard Edition
- Microsoft FoxPro 2.0
- Microsoft FoxPro 2.6a Professional Edition for Macintosh
- Microsoft Visual FoxPro 7.0 Professional Edition
- Microsoft Visual FoxPro 8.0 Professional Edition
- Microsoft Visual FoxPro 9.0 Professional Edition
This article was previously published under Q106708
SUMMARY
You can use the following methods to generate a unique string for a key field value:
- If you are using Microsoft Visual FoxPro 8.0 and later versions, you can create an autoincrement field type.
- You can create a unique value by using Microsoft Windows API functions.
- If you are using earlier versions of FoxPro and of Visual FoxPro, another method is described later.
For additional information, see the "References" section.
MORE INFORMATION
The following code is adapted from the "FoxPro 2.5 Advanced Developer's Handbook" by Adams and Powell, Chapter 18, page 644.
The following function accepts a numeric parameter to specify the length of the key desired. If no parameter is passed, the function will generate a unique key that is 8 characters long. The maximum key length that can be generated is 18 characters.
FUNCTION ukey
PARAMETER lenkey
IF PARAMETERS()=0
lenkey=8
ENDIF
IF lenkey=1
RETURN RIGHT(SYS(3),1)
ENDIF
len1=FLOOR(lenkey/2)
len2=lenkey-len1
key1= RIGHT(SYS(3),len1)
key2= RIGHT(SYS(2015),len2)
RETURN key1+key2
Theoretically, this method allows more possible keys than FoxPro can store records, without a duplicate; however, there is the possibility of duplication because of the cyclic nature of the algorithm (which is based on the system time and date).
The default 8-character return from UKEY() will be sufficient for most systems and the chance of a duplicate is significantly less than 1:10**18. (The actual figure is estimated here: 36**8*10**10). To ensure uniqueness, a SELECT-SQL statement can be used. For example, if you were using a database alias of MYDATA, and a key field of KEYFIELD C(8), you would use the following SELECT-SQL statement:
goodkey=.F.
DO WHILE !goodkey
newkey= UKEY()
SELECT KEYFIELD from MYDATA WHERE KEYFIELD=newkey
goodkey=IIF(_TALLY=0,.T.,.F.)
ENDDO
This method will slow down as the data grows. However, this method is adequate for tables with less than 16 million records. For larger tables, use a key length that is greater than 8.
REFERENCES
FoxPro 2.5 Advanced Developer's Handbook, Pat Adams and Jordan Powell, Brady Publishing, New York, NY, 1993, Chapter 18, page 644
For additional information about creating a unique GUID, click the following article number to view the article in the Microsoft Knowledge Base:
269387 How to obtain a GUID in Visual FoxPro
For additional information about the autoincrement file type, search for "autoincrement fields" in the Visual FoxPro online Help.
Additional query words: VFoxWin FoxMac FoxDos FoxWin 2.50 2.50a 2.50b 2.50c 2.60 unique key avoid duplicate record
Keywords: kbcode KB106708
