DOCUMENT:Q136921  20-AUG-1999  [foxpro]
TITLE   :Guidelines for Creating Unique and Sequential Keys
PRODUCT :Microsoft FoxPro
PROD/VER:
OPER/SYS:
KEYWORDS:

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual FoxPro for Windows, version 3.0 
-------------------------------------------------------------------------------

SUMMARY
=======

You can create unique and sequential keys in Visual FoxPro by using two of
Visual FoxPro's new features: Stored Procedures and Default Values.

MORE INFORMATION
================

To see an example showing how to create unique and sequential keys, please look
at the TasTrade sample application located in the Vfp\Samples\Mainsamp
directory. In this sample, the NewID() stored procedure has been written and
stored in the TasTrade database. The following text is taken from the "Behind
the Scenes" feature of the Tastrade sample:

The NewID() stored procedure creates unique IDs in the system. It returns the
default value for the primary keys for the Supplier, Products, Employee,
Category, Shippers, and Orders tables.

Code in NewID() opens Setup.dbf, looks for table alias in the Key_name field,
reads the current value of the Value field, increments it by 1, and then writes
it back to Setup.dbf. The value that was read from the Value field before
incrementing is then returned as the primary key value for a record.

Note that the NewID() stored procedure is also designed to accept an alias as a
parameter. The same technique could then be used to maintain incrementing values
that were not being used as primary keys. An example of this is the order_number
record, which is used to generate order numbers for the Orders table.

You can modify stored procedures by first opening the database with the OPEN
DATABASE command, and then using the MODIFY PROCEDURES command to bring up an
editing window. Alternatively, you can use the MODIFY DATABASE command, and then
click the Stored Procedures button on the toolbar.

The code for NewID() is as follows:

      FUNCTION NewID(tcAlias)
        LOCAL lcAlias, ;
              lcID, ;
              lcOldReprocess, ;
              lnOldArea

        lnOldArea = SELECT()

        IF PARAMETERS() < 1
          lcAlias = UPPER(ALIAS())
        ELSE
          lcAlias = UPPER(tcAlias)
        ENDIF

        lcID = ""
        lcOldReprocess = SET('REPROCESS')

        *-- Lock until user presses Esc
        SET REPROCESS TO AUTOMATIC

        IF !USED("SETUP")
          USE tastrade!setup IN 0
        ENDIF
        SELECT setup

        IF SEEK(lcAlias, "setup", "key_name")
          IF RLOCK()
            lcID = setup.value
            REPLACE setup.value WITH ;
                    STR(VAL(ALLT(lcID)) + 1, LEN(setup.value))
            UNLOCK
          ENDIF
        ENDIF

        SELECT (lnOldArea)
        SET REPROCESS TO lcOldReprocess

        RETURN lcID
      ENDFUNC

Additional query words: VFoxWin

======================================================================
Keywords          :  
Technology        : kbVFPsearch kbAudDeveloper kbVFP300

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

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.