How to Avoid Missing IDs

[Ams]

Hi Everybody,
I am using VFP 6.
This is a general question.
I have a table ('CodeBank'), in my DBC which stores the name of the
tables/views and the last used IDs.
On Adding new record, I generate a new Id and store it in CodeBank also.
Everything is working fine,except in one condition, in Mutli-User environment.
Suppose two users, simultaneously add records, and the IDs created are 017
and 018. The user who has the 018 ID, saves the record, but the user with the
017 Id, doesnt save the record. This causes the ID to miss.
How can I avoid such missing Ids ?

[Rick Bean]

I guess the first question is why this is important? If it's for auditing purposes, then just log the fact that the 17 wasn't used. A true primary key only needs to be unique and doesn't need to be sequential.

Rick

Post by Ams
Hi Everybody,
I am using VFP 6.
This is a general question.
I have a table ('CodeBank'), in my DBC which stores the name of the
tables/views and the last used IDs.
On Adding new record, I generate a new Id and store it in CodeBank also.
Everything is working fine,except in one condition, in Mutli-User environment.
Suppose two users, simultaneously add records, and the IDs created are 017
and 018. The user who has the 018 ID, saves the record, but the user with the
017 Id, doesnt save the record. This causes the ID to miss.
How can I avoid such missing Ids ?

[Olaf Doschke]

Firstly: Like Rick says: Who cares for gaps?

In principle you can set the Next value of an Autoinc field
by using ALTER TABLE:

CREATE CURSOR curTest (iID I AUTOINC NEXTVALUE 1 STEP 1)
APPEND BLANK
APPEND BLANK
APPEND BLANK
* Records 1,2,3
DELETE NEXT 1
* Record 3 deleted
ALTER TABLE curTest alter COLUMN iID I AUTOINC NEXTVALUE 3
APPEND BLANK
* Record 3 was added again and now exists twice, once deleted once not.

One issue, if you use this as primary key:

The deleted record prevents you from appending
another record with ID 3, as also deleted records
remain in the (primary) index!

So you shouldn't really reset the NEXTVALUE without
PACKing the table, getting rid of the deleted records.

Bye, Olaf.

[Anil Kumar]

Try to generate ID while saving.

Post by Ams
Hi Everybody,
I am using VFP 6.
This is a general question.
I have a table ('CodeBank'), in my DBC which stores the name of the
tables/views and the last used IDs.
On Adding new record, I generate a new Id and store it in CodeBank also.
Everything is working fine,except in one condition, in Mutli-User environment.
Suppose two users, simultaneously add records, and the IDs created are 017
and 018. The user who has the 018 ID, saves the record, but the user with the
017 Id, doesnt save the record. This causes the ID to miss.
How can I avoid such missing Ids ?