Jim Starkey wrote:

>I really don't like this kind of eroteric hocus pocus unless sanctioned
>by the standard, and even then, not much.
>
>If someone wants to add a not null field to a populated table, the order
>of battle should be:
>
> 1. Add the field without NOT NULL
> 2. Update all records in the table assigning appropriate values to
> the new field.
> 3. Alter the field to be NOT NULL
>
>Performing ESP is almost never the solution. It is simpler all around
>to make the guy handle the problem himself. In almost all cases, it is
>nothing more than a single mass UPDATE statement.
>
>

Hi,

It's not surprise to me that most people disagree with the approach I
mentioned.

That is the rule for MSSQL (the version I used was 6.5).

To add a NOT NULL field you must supply a DEFAULT VALUE that is applied
to the new column.

IMHO this is a simple way to prevent a NOT NULL column to have NULL values.

If the new columns should have a calculated, especial, or any other kind
of value other than a default one for every record, the user will just
need to do what you suggested

" 2. Update all records in the table assigning appropriate values to
the new field."


The DEFAULt VALUE is just to not leave a NOT NULL field with a NULL state, and to avoid "the user to forget fill it".

I think that will be good if FB provides a single way to insert a NOT NULL field in the table.

I upgrade my databases using scripts, so it's not a problem for me, but in the past I used CASE tools.

Using a CASE tool is easy to add a new integer field and set a default value of 0 (or something).

But using your approach the case tool will need to generate those statements:
insert a null field.
update to some value
change it to not null

I know it's not Firebird problem what the 3rd party tools needs to do to have it's job done, but I think one single/atomic step to be more error free, and if the user need special values for that field, it will need to calculate/update in any form. I don't disagree with your proposal, and that it is clear on how the steps should be done.

Today in Firebird current state I just add a not null collumn and fill it with data on the next line of the script.

Any one could tell how other databases behave on this point ?

If I recall correct Oracle uses the Jim proposed approach. But when I developed to Oracle I don't used CASE tools anymore, and did the 3 steps in scripts.

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br