Thanks Helen for the reply....it does make sense to me....

One more question, is it a bug (that will or could be fixed), or is it one
of those things that you learn and hopefully never make the same mistake
twice????

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: 16 January 2003 09:32
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] Is this a bug???

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







Yahoo! Groups Sponsor


ADVERTISEMENT

<http://rd.yahoo.com/M=241773.2861420.4212388.1925585/D=egroupweb/S=17051153
86:HM/A=1394045/R=0/*http:/www.hgtv.com/hgtv/pac_ctnt/text/0,,HGTV_3936_5802
,FF.html> HGTV Dream Home Giveaway


<http://us.adserver.yahoo.com/l?M=241773.2861420.4212388.1925585/D=egroupmai
l/S=:HM/A=1394045/rand=778237980>

To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo!
<http://docs.yahoo.com/info/terms/> Terms of Service.


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