FirebirdSQL logo

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:

  1. Create a new domain with the desired characteristics.

  2. Switch all the concerned columns over to the new domain.

  3. 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 [nullguide-nulls-reported-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.

Testing for NULL and equality in practice

This section contains some practical tips and examples that may be of use to you in your everyday dealings with NULLs.It concentrates on testing for NULL itself and testing the (in)equality of two things when NULLs may be involved.

Testing for NULL — if it matters

Quite often, you don’t need to take special measures for fields or variables that may be NULL.For instance, if you do this:

select * from Customers where Town = 'Ralston'

you probably don’t want to see the customers whose town is unspecified.Likewise:

if (Age >= 18) then CanVote = 'Yes'

doesn’t include people of unknown age, which is also defensible.But:

if (Age >= 18) then CanVote = 'Yes';
else CanVote = 'No';

seems less justified: if you don’t know a person’s age, you shouldn’t explicitly deny her the right to vote.Worse, this:

if (Age < 18) then CanVote = 'No';
else CanVote = 'Yes';

won’t have the same effect as the previous.If some of the NULL ages are in reality under 18, you’re now letting minors vote!

The right approach here is to test for NULL explicitly:

if      (Age is null) then CanVote = 'Unsure';
else if (Age >= 18  ) then CanVote = 'Yes';
else                       CanVote = 'No';

Since this code handles more than two possibilities, using the CASE syntax (supported by Firebird 1.5 and up) is more elegant:

CanVote = case
            when Age is null then 'Unsure'
            when Age >= 18   then 'Yes'
            else 'No'
          end;

Or, prettier:

CanVote = case
            when Age >= 18 then 'Yes'
            when Age <  18 then 'No'
            else 'Unsure'
          end;

Equality tests

Sometimes you want to find out if two fields or variables are the same and you want to consider them equal if they are both NULL.The way to do this depends on your Firebird version.