Subject Re: [firebird-support] default field value retrospective behaviour
Author Helen Borrie
At 05:25 AM 19/08/2005 +0000, you wrote:
>Hi Group,
>FB 1.5.2 SS, Win2K
>I have a table, lets call it t. It already contains records.
>I am now adding a new timestamp field, I will call it starttime that I
>want it to default to a long time ago.
>The field is added, but the field is full of NULL values (the irony of
>something being full of NULL amuses me :) Is that behaviour correct,
>or should it fill the field with '1/1/0001's

Yes. Adding a column doesn't do anything to the data in existing rows; so
setting a default for it doesn't magically fill it with the default value,
either. Furthermore, you can add a column with a NOT NULL constraint on it
and immediately make your database unrestorable if you don't go in and
populate your existing rows.

FWIW, a DEFAULT constraint works in one and only one circumstance: on
inserting a row and ONLY if the insert statement does not contain the
defaulted column.

>I know I can run a very simple query to do what I need, and I do not
>object to doing so (that is not what this post is about).
>I was just curious to know how the standard says it should behave.

Just like Firebird. :-)