>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.