>I need to receive more wisdom from all you surrogate key experts out there. What do you think of the Tastrade sample app's way of making them? That is, apart from the fact that it uses a character field, which is easy enough to change. To refresh our memory, the Tastrade database has a table called "setup" whose fields are the names of tables having keys, and the next unused value for each key. The primary key of each table has a default value newid(), which is a stored procedure that looks like this:
>

>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
>
><\pre>
>
>Does it look secure?  I'll probably do it this way unless someone sees a problem.  Thank you.


Looks OK.  But I would use the following in place of RLOCK() :

*--------------------------------------------------------------------------
* _sp_recLock
*
* Description : Attempt to lock a record in specified table.
*
* Creation date	  : 05-SEP-89: Asif Dhanani
* Revision History: 16-SEP-96: Asif Dhanani
*
* Parameters:             liAttempts	&& No. of times to attempt lock
*			  lcRecList	&& String of record numbers to lock
*					&& i.e., '1,5,9' for records 1,5, and 9
*			  lcAlias	&& Table alias in which to lock records
*
* Returns:                lSuccess	&& .T. if Record lock successful
*					   .F. otherwise
*--------------------------------------------------------------------------
FUNCTION _sp_recLock
LPARAMETER liAttempts,lcRecList,lcAlias
LOCAL lSuccess,lnOldReprocess
lSuccess = .F.
lnOldReprocess = SET('REPROCESS')
liAttempts = IIF(TYPE("liAttempts")<>"N",10,liAttempts)
liAttempts = IIF(liAttempts > -2,liAttempts,1)
SET REPROCESS TO liAttempts
lcRecList = IIF((TYPE("lcRecList")<>"C") OR EMPTY(lcRecList),STR(RECNO(lcAlias)),lcRecList)
lSuccess = RLOCK(lcRecList,lcAlias)
SET REPROCESS TO lnOldReprocess
RETURN lSuccess
ENDFUNC	&&_sp_recLock
*--------------------------------------------------------------------------

This should solve record locking contentions if two users try to lock the same record at the same time.

Also key fields that see excessive (every few seconds) locking traffic, require several records in the setup table.  But you must
establish ahead of time the range of values each record would handle.  For example, in the setup table you might have
four records for key_name == 'CATEGORY' : 1..1000000,1000001..2000000,2000001..3000000,3000001..4000000.  Now,
if a record lock failed, all you need is to locate the next available record which is not locked.