--- In firebird-support@yahoogroups.com, "Ryan Thomas" <ryan@t...>
wrote:

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

A known issue. After adding a field with not null, you must then
explicitly fill it with something.

Running a backup and restore after any DDL changes is always a good
idea.

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

I agree, but there is no built in function to add a not null
constraint to a table. You can perform an update on one of the RDB$
tables to do it, there is a null flag that is set, but I am not 100%
sure if anything else is done.

Until a "safe" alter table command exists for setting a field to
nullable or not nullable, that is the only option for adding a
required field to the table.

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

Interesting, could be a bug. Check the tracker if it is a known issue.

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

None that I can think of.

I think everyone agrees that DDL changes shouldn't make a backup
unrestorable.

Adam