| Subject | RE: [ib-support] Database Backup |
|---|---|
| Author | John Bennett |
| Post date | 2003-06-10T03:23:28Z |
Sorry Helen,
I obviously didn't make myself clear enough. The values in the database
still have the default values but the table has not been created to add the
default values in for new records. Is there any way to modify the table
schema to add the default value in after the table has been restored.
Cheers
Johnb
-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Tuesday, 10 June 2003 1:07 PM
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] Database Backup
John,
At 12:33 PM 10/06/2003 +1000, you wrote:
your "default values are missing". If default values were written at the
time the rows were created, then they became physical data, just like any
value that was written there by any other means. Gbak has no way of
knowing that a particular value in a particular column in a particular row
was put there by a default constraint.
A more likely reason is that you *thought* default values should have been
written, when in reality they were not. Defaults work only in a very
restricted set of circumstances, viz.
1. on an insert. (They don't apply to updates at all.)
2. only where the INSERT statement does not include the defaulted column
in its field list.
If you want a default value to be written wherever the column would
otherwise receive NULL, then you have to write Before Insert and Before
Update triggers to make it happen.
As to how to fix the situation one-off, for existing data:
UPDATE atable
set defaulted_col = TheDefault
where defaulted_col is null ;
heLen
ib-support-unsubscribe@egroups.com
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
I obviously didn't make myself clear enough. The values in the database
still have the default values but the table has not been created to add the
default values in for new records. Is there any way to modify the table
schema to add the default value in after the table has been restored.
Cheers
Johnb
-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Tuesday, 10 June 2003 1:07 PM
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] Database Backup
John,
At 12:33 PM 10/06/2003 +1000, you wrote:
>Hi all,Sorry, but you are going to have to look for a more plausible reason why
>
>I have what is (hopefully) a simple question.
>
>We have an Interbase 5.6 database that we recently backed up using gbak.
>We then moved the database to another production machine and restored it.
>Upon further investigation we have noticed that tables with fields that
>contain default values have not been recreated with the default values.
>Is there any way to add the default value to the field without losing the
>data that is already in the table (500,000 records)?
your "default values are missing". If default values were written at the
time the rows were created, then they became physical data, just like any
value that was written there by any other means. Gbak has no way of
knowing that a particular value in a particular column in a particular row
was put there by a default constraint.
A more likely reason is that you *thought* default values should have been
written, when in reality they were not. Defaults work only in a very
restricted set of circumstances, viz.
1. on an insert. (They don't apply to updates at all.)
2. only where the INSERT statement does not include the defaulted column
in its field list.
If you want a default value to be written wherever the column would
otherwise receive NULL, then you have to write Before Insert and Before
Update triggers to make it happen.
As to how to fix the situation one-off, for existing data:
UPDATE atable
set defaulted_col = TheDefault
where defaulted_col is null ;
heLen
>Thanks in advanceTo unsubscribe from this group, send an email to:
>Johnb
>
>
>To unsubscribe from this group, send an email to:
>ib-support-unsubscribe@egroups.com
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
ib-support-unsubscribe@egroups.com
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/