Making an existing column NOT NULL
You cannot add NOT NULL
to an existing column, but there’s a simple workaround.Suppose the current type is int, then this:
create domain intnn as int not null;
alter table MyTable alter MyColumn type intnn;
will change the column type to “int not null”.
If the table already had records, any NULL
s in the column will remain NULL
, and again most Firebird clients will report them as 0 to the user.The situation is almost exactly the same as when you add a NOT NULL
column (see [nullguide-add-not-null-field]).The only difference is that if you give the domain (and therefore the column) a default value, this time you can’t be sure that it will be applied to the existing NULL
entries.Tests show that sometimes the default is applied to all NULL
s, sometimes to none, and in a few cases to some of the existing entries but not to others!Bottom line: if you change a column’s type and the new type includes a default, double-check the existing entries — especially if they “seem to be” 0 or zero-equivalents.
Warning
|
Some Firebird tools allow you to make an existing column |