Subject Re: Database Integrity
Author Adam
--- In, "Ryan Thomas" <ryan@t...>
> 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
> 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

> I guess my question is this: Why does firebird allow a column to be
> with a not null constraint without specifying a default value? When
> 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.

> produced some even greater results by throwing a string conversion
> 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