Subject Re: Two users adding a record with the same code
Author Adam
> Suppose I have a Table of ENTERPRISES with a field CODE (numeric),
NAME, ADDRESS, etc. Then I have a table EMPLOYEES with a field
ENTERPRCODE, NAME, AGE, ADDRESS, etc. Suppose there are already 100
entreprises in the table ENTERPRISES.

Hello Gustavo,

If your CODE is the primary key of the table, then it should be of no
interest to John or Peter. If code is some organisation level
identifier, then surely they already have some mechanism to serialise
the allocation so this can not happen in their current manual process?

Assuming they don't, and you want Firebird to prevent this:


* Pre-allocate the CODE; or
* Design your program so it has some form of CODE conflict resolution.


The easiest way is to call a Generator, and that is your code you can
use in your client application. If someone hits cancel or has their
computer crash or whatever, you will get 'holes' in the code sequence,
which may impact you for say legal rules about invoice numbering. In
this case, John would get 100, Peter 101, but if one cancelled, the
number would never been seen again.

Another way is to create a holding table for CODEs. When your
application wants a code, it must insert it into that holding table
(which has a unique constraint). When finished, you can remove the
code from the table. In this case, Peter would no from the start that
100 was not an option.

Conflict Resolution

The other way to do it is to design your application so that when
Firebird reports the conflict, you present the screen to the Peter
that 100 is already in use, and allow him to choose a different code.