FirebirdSQL logo

Using domains to add a non-nullable field

Instead of specifying data types and constraints directly, you can also use domains, e.g. like this:

create domain inn as int not null;
alter table Adventures add id inn;

Or like this:

create domain icnn as int check (value is not null);
alter table Adventures add id icnn;

For the presence of NULLs in the added columns, returning of false 0's, effects of default values etc., it makes no difference at all whether you take the domain route or the direct approach.The only difference is that domain-based constraints can’t be removed at the column level.So if you ever want to drop the constraint later, you must either switch the column to another domain or built-in type again, or remove the constraint from the entire domain.The latter operation is described in the section [nullguide-change-domain-nullability].

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].