At 10:17 PM 19/05/2009, you wrote:

>I have a table called "Readers" in a Firebird 2.1 database (FlameRobin
>reports the server version as "WI-V2.1.0.17798 Firebird 2.1"). In the
>latest version of the front-end software driving this database I update
>the table using the following SQL:
>
>ALTER TABLE "Readers" ADD "AlwaysMonitor" SMALLINT NOT NULL;

...and COMMIT the transaction as soon as it has performed the DDL? This is essential, before you proceed to perform any DML on the existing rows.

>Since the field (like others in the table) must not be NULL, the
>front-end software then updates all existing records to have a value of
>0, and this update is successful (no errors are reported, and FlameRobin
>shows the field as being set to 0, not NULL).

At best at this point (hoping you *did* commit the transaction that performed the DDL before you tried to update the data), you have a transaction with uncommitted DML. As long as it is uncommitted, it remains visible only to the transaction that performed it. That's a problem for you if you don't understand that that is how a transactional database system is designed to behave.

>However, if I then try to update any other field, I get an error
>reported. DAO reports it as error 3197, but FlameRobin reports:
>
>The insert failed because a column definition includes validation
>constraints.
>Engine Code: 335544347
>Engine Message:
>validation error for column AlwaysMonitor, value "***null***".

Different transactions. An INSERT is not an update. However, the other transactions are aware that the new column and its constraint exist, so the validation fails in the newer transactions in order to preempt an inconsistent state.

>The problem is that AlwaysMonitor isn't NULL; it's 0.

To the later transactions, it is still null.

> Note also that
>there is no error message reporting other similarly constrained fields
>as being set to NULL. However, if I edit the value of AlwaysMonitor
>(eg I change it from 0 to 1), and then change whatever field I really
>wanted to change, all is OK; I can even change the value of
>AlwaysMonitor back to 0 afterwards.

Lessons learnt: totally separate DDL from DML; perform *and commit* essential DML (such as populating constrained new columns) *before* admitting access to other transactions; exceptions ultimately are the way that the database protects the consistency of data state and interpreting them is a vital part of application design and workflow.

>The front-end software is able to work with other database servers. It
>modifies the "Readers" table using the same method when working with
>MySQL (the only difference being the delimiters in the SQL), and
>everything works as intended.

This isn't MySQL: it's an ACID-compliant, transactional database management system. The MySQL you're talking about is not ACID-compliant and can't protect itself from consistency violations; and it's non-transactional. (Some of its non-free backends do both consistency protection and transactions, however.)

>Can anyone tell me please why Firebird is having problems with this
>table modification, and more importantly, what I should do about it?

Learn to appreciate the importance of consistency and it will become clearer why MySQL's behaviour has led to your misconception that consistency violation exceptions are "problems to be worked around".

>I need to modify other tables in a similar way, so the clunky workaround I
>discovered is not really an option.

Clunky workarounds to fix avoidable problems are not a safe practice at all. Stick to the road and watch for the signposts!

1. Perform structural changes - that's *any* DDL - with exclusive access, as Owner of the object, in their own transactions and commit immediately.

2. Follow up immediately with any requisite DML (like your population of a non-nullable new column) in its own transaction and commit immediately.

3. When there is existing data, always be prepared to catch exceptions, interpret them and provide appropriate action (roll back and step back).

Note, if you're planning to impose referential integrity constraints then there is potential for existing data to be in violation. For this type of task, there's a Step Zero: to check and fix up data in columns that will be involved in the constraints - and commit the work before you proceed to Step 1, of course!

./heLen