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

Making non-nullable columns nullable again

Removing a NOT NULL constraint

If you gave the NOT NULL constraint a name when you created it, you can simply drop it:

alter table Adventures drop constraint IdNotNull

If you forgot the name, you can retrieve it with isql’s `SHOW TABLE command (i.c. SHOW TABLE ADVENTURES);other clients may have their own provisions to let you find or browse constraint names.

If you didn’t name the constraint explicitly, Firebird has created a name for it, but SHOW TABLE won’t display it.You have to use this piece of SQL to dig it up:

select rc.rdb$constraint_name
from   rdb$relation_constraints rc
       join rdb$check_constraints cc
       on rc.rdb$constraint_name = cc.rdb$constraint_name
where  rc.rdb$constraint_type   = 'NOT NULL'
       and rc.rdb$relation_name = '<TableName>'
       and cc.rdb$trigger_name  = '<FieldName>'

Don’t break your head over some of the table and field names in this statement;they are illogical but correct.Make sure to uppercase the names of your table and field if they were defined case-insensitively.Otherwise, match the case exactly but don’t enclose the names in double-quotes like you would do in a regular query.Also don’t include the angle brackets (<>). Once you have the constraint name, you can drop it just like in the previous example.

Tip

If the above statement returns an empty set and you are sure that you’ve correctly filled in the table and field names (including case!), and the constraint did not come from a domain either (this is discussed in the next sections), it may be that a third-party tool has made the column NOT NULL by setting a flag in a system table.In that case it’s probably best to remove it again with the same tool.If that is not an option, check the field’s NULL flag with:

select rdb$null_flag from rdb$relation_fields
where  rdb$relation_name  = '<TableName>'
       and rdb$field_name = '<FieldName>'

If the flag is NULL or 0, the field is nullable (at least as far as this flag is concerned).If it’s 1, clear it with:

update rdb$relation_fields set rdb$null_flag = null    /* or 0 */
where  rdb$relation_name  = '<TableName>'
       and rdb$field_name = '<FieldName>'

followed by a commit.
Important

As soon as you’ve dropped the NOT NULL constraint — by whichever method — SHOW TABLE will report the column as nullable.Any existing NULLs that were previously illegal and therefore hidden by most clients (see [nullguide-nulls-reported-as-zeroes]) will become visible again.

However, before you can insert NULLs into the column, you must commit your work, close all connections to the database, and reconnect.