Hi all,

I think I may have found a bug in Firebird 1.0.0.796 and I've looked through
the known bugs and did not find it anywhere...Can someone confirm what is
going on here...

I have a database updater application that patches client databases and
brings them up to the most recent versions. In this version release I had to
add 2 columns to a System_Reference table that only contains 1 record, has
no PK, and 1 foreign constraint called FK_RELATION_71.

I then performed the following statements...

Step 1:
Start transaction

Step 2:
alter table System_Reference add CON_NOTE_COPIES integer;

Step 3:
update System_Reference set
CON_NOTE_COPIES = 2

Step 4:
alter table System_Reference add CON_NOTE_PIECE integer

Step 5:
update System_Reference set
CON_NOTE_PIECE = 0

On executing the SQL in step 5 I got a foreign constraint violation on
FK_RELATION_71. The FK_RELATION_71's definition is that the column
CONSIGNMENT_ALLOCATION_ID must reference a value in the
CONSIGNMENT_ALLOCATIONS table. After playing around a bit, I found that the
SQL in step 5 worked successfully if I changed it to read

update System_Reference set
CON_NOTE_PIECE = 1

But the I noticed that it had changed the value in the
CONSIGNMENT_ALLOCATION_ID from 5 to 1?!?!?!?!??!

To fix the problem I did the following:

Step 1:
Start transaction

Step 2:
alter table System_Reference add CON_NOTE_COPIES integer;

Step 3:
Commit Retaining

Step 4:
update System_Reference set
CON_NOTE_COPIES = 2

Step 5:
alter table System_Reference add CON_NOTE_PIECE integer

Step 6:
Commit Retaining

Step 7:
update System_Reference set
CON_NOTE_PIECE = 0

By committing the transaction after each alter table, the problem was sorted
out....

Any light on what happened here???

TIA
Steve



[Non-text portions of this message have been removed]