DOCUMENT:Q106708  24-AUG-1999  [foxpro]
TITLE   :How to Generate Unique Key Field Values
PRODUCT :Microsoft FoxPro
PROD/VER:2.5x 2.6x 3.00 | 1.02 2.00 2.5x
OPER/SYS:
KEYWORDS:kbcode

======================================================================
-------------------------------------------------------------------------------
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, versions 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[ASCII 146]s Handbook," Pat Adams and Jordan
Powell, Brady Publishing, New York, NY., 1993, Chapter 18, page 644

Additional query words: VFoxWin FoxMac FoxDos FoxWin 2.50 2.50a 2.50b 2.50c 2.60 unique key avoid duplicate record

======================================================================
Keywords          : kbcode 
Technology        : kbHWMAC kbOSMAC kbVFPsearch kbAudDeveloper kbFoxproSearch kbZNotKeyword3 kbFoxPro260aMac kbFoxPro200DOS kbVFP300
Version           : 2.5x 2.6x 3.00 | 1.02 2.00 2.5x

=============================================================================

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.

Copyright Microsoft Corporation 1999.