At 08:47 AM 16/01/2003 +0200, you wrote:

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

Yes - you learned in an interesting way that it's indeed true, yea, verily,
that it's a very bad idea to mix up DML with uncommitted DDL transactions.
This rule applies any time, of course, but nowhere is it more useful to
follow it than when constraints are in the equation...since metametadata
will be affected by commit or rollback.

heLen