FirebirdSQL logo

Adding a CHECKed column

Using a CHECK constraint is another way to disallow NULL entries in a column:

alter table Adventures add id int check (id is not null)

If you do it this way, a subsequent SELECT will return:

Table 1. Result set after adding a CHECKed field
Name Bought Price ID

Maniac Mansion

12-Jun-1995

$ 49,--

<null>

Zak McKracken

9-Oct-1995

$ 54,95

<null>

Well, at least now you can see that the fields are NULL!Firebird does not enforce CHECK constraints on existing rows when you add new fields.The same is true if you add checks to existing fields with ADD CONSTRAINT or ADD CHECK.

This time, Firebird not only tolerates the presence and the backing up of the NULL entries, but it will also restore them.Firebird’s gbak tool does restore CHECK constraints, but doesn’t apply them to the existing data in the backup.

Note

Even with the -n switch, gbak restores CHECK constraints.But since they are not used to validate backed-up data, this will never lead to a failed restore.

This restorability of your NULL data despite the presence of the CHECK constraint is consistent with the fact that Firebird allows them to be present in the first place, and to be backed up as well.But from a pragmatical point of view, there’s a downside: you can now go through cycle after cycle of backup and restore, and your “illegal” data will survive without you even receiving a warning.So again: make sure that your existing rows obey the new rule immediately after adding the constrained column.The “default” trick won’t work here;you’ll just have to remember to set the right value(s) yourself.If you forget it now, chances are that your outlawed NULLs will survive for a long time, as there won’t be any wake-up calls later on.

Note

The isql command SHOW TABLE lists “CHECK …​ IS NOT NULL” columns as nullable, because the column type is not intrinsically NOT NULL.But it also shows the CHECKs, so you know how things stand.

Likewise, the engine describes these columns as nullable when a query is executed.This accounts for the fact that NULLs are truthfully reported in this case, as you’ve seen in the table above.

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.