>I'm looking for different ways people assign unique keys in multi user environments. My boss would like to use the record number, which sounds like a bad idea to me given that we would never be able to pack the table among other issues. Do we need to get more sophisticated than setting the order to the right index and doing:
>GO BOTT
>nNewID=myTable.key+1
>INSERT INTO myTable (key) VALUES(nNewID)
>
>Would it make sense to have a property hold the next value and put code in the access method to encapsulate the process? Thanks for any feedback on this basic task.
>
>Marcus.
Hi Marcus,
Simply recno() is not the answer, wish it could be. With VFP even considering you would never pack, you'd be guaranteed to get duplicate negative numbers used as recno() in a buffered table till commit.
Problem is divided into 2 sections.
-To generate a unique value
-How and where to apply that value
-To generate a unique value :
Many use integer keys as primary keys. They're produced basically keeping another table with two fields. One for tablename, other for next available key. You should lock the record while getting new key. There I strongly suggest to put a slight delay if rlock() fails, otherwise all users except the first putting rlock() could get into an infinite wait state. Also therotically integer keys have a chance to exhaust (just therotically not in practice). Assuming a question would come how could they exhaust consider this :
-You get the lock and increment the value, later decide not to save. You haven't used the key but exhausted one key value. If this happens frequently and your table get near its 1 billion limit your key value could exceed an integer limit as well. Too much theory but possible :)
Anyway I'm not in the majority and prefer char keys. Sys(2015) on a single computer, provided you check computer datetime() is not before lupdate() always produces a unique 10 char value (9 in fact, first letter is always an underscore). In bulk operations it's faster to produce than rlock()+integer. Now you should be sure it's unique accross the network too. What I do is to use a users table where each user has only one record defining it. I could keep anything I want in that table, user's login id, login time, logout time, login computer's TCP/IP, netbios name etc. If you consider this table could have up to 65535 records (a huge number:) then you use that table's bintoc(recno(),2) to get a 2 char value to uniquly identify a user.
2 char user id + 9 char per computer sys(2015) provide a unique reliable primary key.
-How and where to apply.
Well what I do is to use default field values. For every table I create I keep 2 fields :
1 pId for parent id (foreign key) and 1 cId for table's primary key. Their default values are :
pId : Just empty for topmost parent (no parent).
iif(used("parent"), parent.cId, "")
cId :
gcpk() A stored procedure that returns 2+9 char unique value.
Well wasting 7 bytes per key maybe but that's what I use :)
Cetin
