--- In firebird-support@yahoogroups.com, "Brian L. Juergensmeyer"
<brianj@c...> wrote:

> In my opinion, the problem here is in the design of the app.

*grin* I'm not going to argue against *that* ;) It's an existing
application; it's behaviour cannot be changed without much grief from
our users.

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

I have that, it's the user id. A user also has a mailbox ID, which
must also be unique.

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

What the 'meaning' of the mailbox is is up to the customer to decide.
It could be phone numbers, it could be case IDs for a lawfirm, etc.
All we know is that their numeric, and that they will be unique.

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

I suppose. I'll just set the mailbox to UNIQUE but allow NULL. The
application will have to take care of setting an appropriate value.

> 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),

I expected that. I'm not going to be doing many inserts on that table,
but I'll still just have the app logic decide. The UNIQUE DEFAULT NULL
will take care of the uniqueness; what remains is that I'll have to
device a app-side way to generate 'random' unique IDs when the user
wants that (and it'll have to be the default behaviour).

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

I can't guarantee it, so that means it's out. That also answers
another (only slightly related) issue I posted a few days ago. Damn.

Emiliano