> > user the key is already used and that if he doesn't fill in the
> > appropriate field, one will be generated automatically (although
> > would personally stick with generators)
>
> So would I, but here's the situation: we have a 'mailbox ID' that is
> generally random (doesn't matter, as long as it's unique). Some
> organizations, however, want to specifically set this ID at some point
> (to phone numbers, personnel ID, what-have-you), and this change may
> have to happen on a database that is already deployed.
>
> What I might do is set the field to NULL, and offer the user to
> auto-generate them later (just loop through a generator and retry on
> unique constraint conflict until I've got them all). Not pretty, but
> it could work.

Hello, Ameliano,

In my opinion, the problem here is in the design of the app. You're trying
to bend over too far backwards to guarantee a unique key entry for your
table. I think your best bet would be to add an ID field that only and
forever uses a generator to guarantee a unique id. If you feel the need to
do so, set up a UNIQUE index or constraint on the relevant fields that you
mentioned. Then, just do a SELECT FOO FROM MAILBOX WHERE (ID=A_ID OR
PHONE=A_ID OR PERSONNEL_ID=A_ID).

Trying to fight the generators like this is going to cause you nothing but
grief.

And I sincerely hope that, if you do try to do a BEFORE INSERT OR UPDATE
trigger to check for uniqueness, that you:
A: aren't doing much inserting (as doing a bulk insert that triggers a WHILE
(EXISTS.... on each insert would be incredibly painful from a performance
perspective),
and
B: can guarantee that only one user is able to update your mailbox table at
once. If you can't guarantee that, you are going to get into troubles
anyway. If you have two simultaneous transactions open at once entering an
ID manually, you'll end out not seeing the ID's being added manually in each
transaction. Then, when the second (or subsequent) transaction(s) commit,
they'll receive duplicate key errors. Generators are guaranteed to be
unique even across transaction boundries, on the other hand.

HTH,

Brian