>But I've always had a primary key, though I've always controlled it
>programmatically (in single user or file server architectures) - it was
>various user ids, customer ids, item ids etc. Most of the time ordinal
>numbers didn't do - there were lots of legacy coding systems, so we just
>checked for existence of a new (user-supplied) key, and shown the record
>using that key. So, inventing a key just before saving the data couldn't
>work - the key had to be the first field on the form, and it had to be
>checked for uniqueness.
>
>So now I have a double problem - inventing a (surrogate) primary key,
>which can be automatized, and the old problem once more - avoid the
>clash between possible natural unique keys. Since the key is entered as
>a first field, and it does take some time 'till the user gets to the
>last and saves, other user may have gotten the same key from the key
>offering routine; the third and fourth got another ones. Now the first
>three users revert and don't save - I have three unused keys. I don't
>mind, but tax collectors are very suspicious if the invoice numbers are
>not exactly consecutive.
>
>Did anyone solve this problem already?
I put up a modal box and query for the "natural" key, then I search for any occurance. If I find it I display it. If I do not find it then I insert a new record, using an automatically generated large number (but kept hidden) as the primary key, but showing the "key" that the user entered.
Nebraska Dept of Revenue