>Hi Andrea,
>
>>Uhmm, things are getting darker. What does 'surrogate keys' mean for you ?
>My definition would be Artificially generated primary keys values which the user doesn't see.
Cool term! I'll have to remember that one
>I guess I went a little too fast here. The NewId() function would do exactly what you said. Moreover, the problem would come when the user adds a record and then Discards/Cancels it rather than saving it. When you add a record, the NewId() function would update the id in the ID table. When you discard/cancel the record the id is the incremented one so you lose one id in between.
You would not
HAVE to lose an ID in this case. although this is the easiest way.
If you are concerned about making the ID numbers tight with no gaps, then structure your new Id table like this:
TYPE NUMBER
NEW 000564
SALVAGE 000563
When you go to add a new ID, FIRST search for a SALVAGE type...If found() Lock it, use it, deleted it. If its NOT found(), search for a NEW type...lock it, increment it, unlock it.
And no matter what...If the save falls through, Insert the unused ID number into the table as a SALVAGE type.
In otherwards...The salvage type will always hold FAILED ID numbers...and you will always use them first.
That would be me!