Thanks Adam and Nigel,

We've fixed up the customers database so that we can backup / restore
successfully.

We're running 1.5.2 Classic at the customers site (CS and SS in the office).

I'll check out the tracker for the smallint emptystring issue.

Cheers,

Ryan Thomas
TransActive Systems

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

> -----Original Message-----
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
> Sent: Thursday, 1 December 2005 10:49 AM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Re: Database Integrity
>
> --- 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
>
>
>
>
>
>
> ------------------------ Yahoo! Groups Sponsor
> --------------------~-->
> Most low income households are not online. Help bridge the
> digital divide today!
> http://us.click.yahoo.com/I258zB/QnQLAA/TtwFAA/67folB/TM
> --------------------------------------------------------------
> ------~->
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>