Is There A Better Technique For Creating A Unique ID Field Than... 2

[drosenkranz]

Is There A Better Technique For Creating A Unique ID Field Than

Append Blank
ClientID = Reccount() + 1

Any advice?
The 2nd mouse gets the cheese.

 

[rgbean]

If you installed the sample code from the MSDN CD, then start up the TasTrade application:
DO (_samples+"\tastrade\tastrade.app&quot

Choose "Behind The Scenes", in the ComboBox choose "Stored Procedures", and in the Design Feature ListBox choose NewID(). Read the "How It Works" and click on the "Code" button to see the code.

Variants on this routine are normally suggested as "good technique" in a multi-user environment.

Rick

 

[foxdev]

I definitely wouldn't use the technique you described, since packing or copying the file with deleted records would reduce RECCOUNT(), and thus allow the possibility of duplicates going forward.

I've usually used an Integer field, and a separate table (let's call it KEYS) that contains the last-used PK value for each of the tables. I then have a UDF that I call to return the next available number. Robert Bradley

http://www.foxdev.com/

Got extra money lying around? Visit:

http://www.agrainofhope.org

 

[jonscott8]

Check out Vlad's FAQ: faq184-259 Jon Hawkins

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed

 

[wbstrider]

integer field and the rand() function works great for me since the odds of duplication are astranomical. and the interger field only takes 4 bytes.

 

[JNC73]

Hi,

I would use a key table in the database.

This means an extra table which holds for every table a range of keys that can be used and the last used key value.

When you insert a new record in the database make sure your application updates the key table and the data table as one transaction. This way you can always be sure that no one can use the same key twice.

i.e. You have a table customers which holds all the customers of your store. The database will contain the next two tables

Table KEY
Fields TABLE FIRST_KEY LAST_KEY LAST_GIVEN
CUSTOMER 1 9999999 34

Table CUSTOMER
with the last record with key identifier 34.

When you insert a new customer first determine the key with the value in the KEY table and add one with that value. Imidiatley update that value in a transaction.

Then insert the new customer in the table in the same transaction.

When both the insert and update don't result in an error you can commit the transaction.

Hope this helps.


JNC73