How to Generate Unique Key Field Values

 Last reviewed: April 29, 1996
Article ID: Q106708
The information in this article applies to:
  • Microsoft Visual FoxPro for Windows, version 3.0
  • Microsoft FoxPro for Windows, versions 2.5x, 2.6x
  • Microsoft FoxPro for MS-DOS, versions 1.02, 2.0, 2.5x, 2.6x
  • Microsoft FoxPro for Macintosh, version 2.5x, 2.6a

SUMMARY

There are several ways to generate a unique string for a key field value. One method of doing this is described below.

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, but should be sufficient for databases with less than 16 million records. For larger databases, use a key length 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

Additional reference words: VFoxWin 3.00 FoxMac FoxDos FoxWin 1.02 2.00
2.50 2.50a 2.50b 2.50c 2.60 2.60a unique key avoid duplicate record
KBCategory: kbprg kbcode kbhowto
KBSubcategory: FxprgSql

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 29, 1996
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.