Changing the nullability of a domain

Adding and removing domain-level CHECK constraints
Altering a domain's NOT NULL setting

When you create a domain, you can optionally specify NOT NULL and/or a CHECK constraint:

create domain posint as int not null check (value > 0)

Domain constraints cannot be overridden or switched off at the column level, but they can be added to. For instance, you can create a nullable domain but specify NOT NULL for certain columns based on that domain. Or you can define an additional CHECK on the column level. But sometimes you may want to change the constraints for the entire domain after it has been used for a while. The following paragraphs show you how.

Adding and removing domain-level CHECK constraints

If the domain doesn't have a CHECK constraint yet, you can add one like this:

alter domain MyDomain add constraint check (value is not null)

You may leave out the constraint keyword if you wish. The added CHECK takes effect immediately for all the columns that are based on the domain. However, the check is only applied to new updates and inserts; existing NULL data will remain in place and will continue to be shown as <null> in result sets.

A domain can have at most one CHECK constraint. There is no ALTER CHECK statement; if you want to change the CHECK, you must drop it and create a new one.

This is how you drop a CHECK constraint from a domain:

alter domain MyDomain drop constraint

You must close all connections and reconnect before you can insert values that would have violated the dropped CHECK in MyDomain-based columns.

Altering a domain's NOT NULL setting

Once a domain is created, Firebird doesn't allow you to add or remove a NOT NULL constraint (DROP CONSTRAINT will only drop a CHECK). If you want to change a domain-wide NOT NULL setting, the official procedure is:

Create a new domain with the desired characteristics.
Switch all the concerned columns over to the new domain.
Drop the old domain.

This is fine when it only concerns a few columns, but what if there are dozens or even hundreds? It is possible to change the setting by going directly to the system table. Be aware however that Firebird does not recommend or support this type of operation, nor is it guaranteed to keep working in future versions. That being said, it's a relatively simple operation and if properly executed it shouldn't cause you any problems.

So here's the SQL, but remember: at your own risk!

update rdb$fields
  set rdb$null_flag = <value> where rdb$field_name = '<DomainName>'

To make a domain NOT NULL, <value> should be 1. To remove a NOT NULL constraint, use 0 or NULL.

Write the domain name in all-caps if it was created case-insensitively; otherwise, match the case exactly. Don't use double-quotes and don't include the “<>”. Also note that, even when DDL autocommit is on (which is the default in isql and many other clients), this statement won't be autocommitted because technically it's not DDL. So don't forget to commit!

If you have set the flag to 1, a subsequent SHOW DOMAIN will immediately report the domain as being NOT NULL. Likewise, SHOW TABLE will list all columns based on the domain as NOT NULL. But if those columns already contained NULLs, a SELECT still truthfully reports them as such (the result set column is still “described” as nullable) – for now. You won't be able to get any new NULLs in the column though. Close all connections and reconnect, and any illegal NULLs will appear as zeroes (at least in most clients; if this surprises you, read False reporting of NULLs as zeroes, earlier in this guide).

If you have changed the flag from 1 to 0 or NULL – making the domain nullable – SHOW DOMAIN and SHOW TABLE will immediately report the domain and “its” columns as nullable. But you still can't insert NULLs in the columns, and any present NULLs are still shown as zeroes (in most clients). Close all connections and reconnect to straighten everything out.

Lastly, please be warned again that this type of fiddling with the system tables is not recommended or supported by Firebird, and not guaranteed to keep working in future versions. If the number of columns based on the domain is limited, it's better to switch them over to another domain or built-in type and then drop the old domain.