Hi,

I've had a look in the archives but I cant seem to find anything on this (I
could of sworn that I'd seen it somewhere before).

We've just had a customer who can no longer restore their database backups
because someone has added a column with a not null constraint to one of the
existing tables. This then filled the existing rows with a null value in the
added column... So at restore time it tries to insert null data into a not
null field.

I guess my question is this: Why does firebird allow a column to be added
with a not null constraint without specifying a default value? When adding
this column it destroys the database integrity by clearly violating one of
the constraints.

After this I had a play around with adding columns, I was able to add a
column of type smallint with a default value of the empty string ''. This
produced some even greater results by throwing a string conversion error
whenever I tried to select anything from this table.

Is there a reason why firebird allows a default value different to that of
the column type?

Below is the error message and the code that I have used to add the columns.

If anyone is able to clear up these queries it would be greatly appreciated,
we can easily work to within the requirements of firebird, I'd just like to
know why we can do such things to the database.

Error Message:
ISC ERROR CODE:335544334

ISC ERROR MESSAGE:
conversion error from string ""

alter table mbuser
add test1 char(20)
not null

alter table mbuser
add test2 smallint
default ''
not null

Cheers,

Ryan Thomas
TransActive Systems

P: (02) 4322 3302
F: (02) 4325 1141
E: ryan@...
W: http://www.transactive.com.au/



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