FirebirdSQL logo

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 NULLs 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 NULLs, 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 NOT NULL with the click of a button.They do this by poking a value directly into a system table.This technique is neither recommended nor supported by Firebird, and although until now it works in practice, this may not be the case in future versions.It’s better to stay safe and use the SQL given above.

Adding a CHECK constraint to an existing column

To add a CHECK constraint to a column, use one of the following syntaxes:

alter table Stk add check (Amt is not null)
alter table Stk add constraint AmtNotNull check (Amt is not null)

The second form is preferred because it gives you an easy handle to drop the check, but the constraints themselves function exactly the same.As you may have expected, existing NULLs in the column will remain, can be backed up and restored, etc.etc. — see [nullguide-add-check-not-null-field].