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;

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).

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***".

The problem is that AlwaysMonitor isn't NULL; it's 0. 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.

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.

Can anyone tell me please why Firebird is having problems with this
table modification, and more importantly, what I should do about it? I
need to modify other tables in a similar way, so the clunky workaround I
discovered is not really an option.

Regards,

Robert.

----------


----------


No virus found in this outgoing message.
Checked by AVG - www.avg.com
Version: 8.5.339 / Virus Database: 270.12.34/2122 - Release Date: 05/19/09 06:21:00


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