Subject Re: Foreign Key implementation
Author Adam
Hello Set,

> I find what you report sensible. Think about the scenario if
transaction 2 had been allowed to update, and then transaction 1 would
continue and update emp_no on the record it already has updated.
Should it get an error then?

Then you must find this not sensible:

insert into employee_project (emp_no, proj_id) values (4, 'GUIDE');
[don't commit]

update employee set salary = salary+100 where emp_no=4;

Firebird is perfectly happy to do it in this order, but surely the
same logic should disallow this?

Or another example.

update country set currency='foo' where country = 'USA';
[don't commit]
update employee set salary = salary+100 where emp_no=4;
[this works]
insert into employee (...., country) values (....., USA);
[this does not]

> It already has uncommitted changes to that very record. I find it a
bit strange if what I'm allowed to do to a record would change between
one update of the record and the next within the same transaction, but
I'm no 'transaction expert' and could well be wrong.

Maybe I was not clear. I am not suggesting to be allowed to change the
key field. We use surrogate keys that never change. I can not imagine
a scenario where allowing the insert would compromise. Yes it could
create a lock conflict if tr1 subsequently attempted to cascade an
update or delete, but this is no different to what would happen if
another other transaction had an uncommitted update on any child record.