Knowledge Base |

# How to generate unique key field values in FoxPro and in Visual FoxPro

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