Hello Group,

My question relates to Foreign Keys and their implementation in
Firebird. My observations relate to 1.5, so please let me know if this
is a resolved issue in a later version.

I will use the Employee database as an example. Consider the tables
Employee and Employee_Project. The Employee_Project table has a
foreign key Emp_No.

If the salary field in Employee is updated in transaction 1 and you
attempt to insert a record into Employee_Project for that employee in
transaction 2.

If transaction 2 is a WAIT transaction, the insert will hang until
transaction 1 commits.
If transaction 2 is a NO WAIT transaction, the insert will fail with a
foreign key violation immediately.

To duplicate in iSQL, open two windows.

Window 1:
update employee set salary=salary+10 where emp_no=4;
[do not commit yet]

Switch to Window 2:
SET TRANSACTION READ WRITE NO WAIT SNAPSHOT;
insert into employee_project (emp_no, proj_id) values (4, 'GUIDE');

Returns:
Statement failed, SQLCODE = -901

lock conflict on no wait transaction
-violation of FOREIGN KEY constraint "INTEG_40" on table
"EMPLOYEE_PROJECT"

I can perfectly understand this behaviour if transaction 1 modified
the emp_no field, but I can not see why it is necessary for an update
of an unrelated field. Is this an implementation limitation in
Firebird or a deliberate design choice? If it is a design choice, then
what is the reason? Why would an update on the child table ok but an
insert disallowed? Why would it be OK if the child row was updated
before the insert is run? If it is an implementation limitation, is it
fixed, planned to be fixed, or should I add it to the tracker?

A WAIT transaction is inappropriate in my particular case (well at
least out of the question until I can assign a timeout value and the
monitoring tables are at a release version so we can see what is
holding a lock). At the moment it looks like I am left to drop the
foreign key declarations and rewrite them as insert and triggers in
the child and delete triggers in the parent. I would prefer not to
have to do this because foreign keys also serve as a self documenting
relationship.

How do other Firebird users deal with this scenario?

TIA
Adam