>This entire thread underlines why many of us are using ONLY surrogate keys,
>which can be updated invisibly from a table. Any field the user edits is
>not included in the primary index. If the user is entering a primary key in
>a multi-user environment, there is ALWAYS going to be a period of time
>while the key can be duplicated by another user.
>
>Barbara (now stepping off her soap-box)
Step back on it, you have a question from the audience. All these months
I've been wandering what's the big deal on using PKs if they serve no
purpose, i.e. contain no meaningful data (remember the dispute on
'natural keys' two months ago). Now I've tried some ODBC on my machine
(exporting some log tables into Excel) and I've discovered, much to my
surprise, that SQL updates require some PK or they scream out bloody
murder if you don't supply it. OK, it's a SQL requirement and a bit of
system overhead, but the deletion mark is such a thing and we tolerate
it for years. OK.
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?
