Internal functions and directives
Conditional statements and loops
Changing the nullability of a domain
Testing for NULL and equality in practice
Firebird Null Guide: NULL behaviour and pitfalls in Firebird SQL
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 |
CHECK
constraint to an existing columnTo 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 NULL
s in the column will remain, can be backed up and restored, etc.etc. — see [nullguide-add-check-not-null-field].