>>>Yes, I use a similar "SYSKEY" approach for generating my primary keys and that works fine. Those fields are never seen by the user and are never changed. My candidate_key field however is seen and can be modified by users. Some come from pre-existing data (Inmate Booking ID No.) so I'm not starting from scratch. What I want to be able to do is allow existing numbers to be used but add any new numbers sequentially (with the first two digits representing the last two digits of the year for a 6 digit number). If a new entry is made which uses a pre-existing # (user would edit the system generated number)I want to be able to ensure that the number doesn't already exist. Can this be done reliably?
>>
>>Depending on the Database platform... yes. If you're using tables that you can "lock" this can be done pretty simply. Now, before you start "casting stones", read the whole message.
>>
>>What you have to do is FLOCK() the file that you are inserting the "potential" duplicate into long enough to add your record. Check for your duplicate and then as soon as the record is inserted and your changes committed, UNLOCK.
>>
>>This has the effect of making the users "stand in line" momentarily to add records and will ensure that two users don't simultaneously add the same key.
>>
>>*NOTE: It may seem that this method would be slow but in my research on the unique key article I wrote for the March '96 issue of FPA, the process was actually faster than without using FLOCK(). I can't remember the specifics though.
>>
>>Hope this gives you some ideas.
>
>Is FLOCK() to add just one record too brutal way? Just imagine that besides multiple users, you also have some multiple update processes etc? AFAIK, ID/record generating process involves RLOCK() against separate table with system counters/keys.

I know what you're saying, Ed, but if you'll re-read the message, I believe you'll see that he's talking about the user being able to sometimes specify the primary key. In this case, you need something like FLOCK() to ensure that duplicate keys are not entered. The key is to structure the checking algorithm so that you FLOCK(), do your check, and UNLOCK as quickly as possible.

Besides, INSERT-SQL does an FLOCK(), too. You just don't *see* it.< g >