How to alter column from NULL to NOT NULL and vice versa?

In Firebird 3 and above, you can use ALTER TABLE:

ALTER TABLE t1 ALTER c1 { DROP | SET } [NOT] NULL;


Firebird validates all the data when ALTER TABLE is executed, so make sure you run appropriate UPDATE statements to make sure all the values are valid before running ALTER TABLE.

In earlier versions of Firebird it is not possible to do it via ALTER TABLE ... ALTER COLUMN statement, but there are workarounds. You have two options:

1. add a new column, populate it, drop the old column and rename the new column to old name. For example if you have a table T1 with column C1, data type integer, it would go like this:

ALTER TABLE T1 add C1_TEMP integer NOT NULL;
COMMIT;
UPDATE T1 set C1_TEMP = C1;
COMMIT;
ALTER TABLE T1 drop C1;
ALTER TABLE T1 alter C1_TEMP to C1;


If your column has a lot of dependencies, you can drop them, make the change and then recreate them. FlameRobin has 'Generate Rebuild Script' option at table's properties page, which builds the script for you.


2. If is also possible with a simple system-tables change. While toying with system tables is usually not a good idea, changing the NULL option for a column is a common task, and this has been tested by many users for many years - as all FB admin tools also used to do it that way:

UPDATE RDB$RELATION_FIELDS SET RDB$NULL_FLAG = 1
WHERE RDB$FIELD_NAME = 'C1' AND RDB$RELATION_NAME = 'T1';


To change from NOT NULL to NULL, use this:

UPDATE RDB$RELATION_FIELDS SET RDB$NULL_FLAG = NULL
WHERE RDB$FIELD_NAME = 'C1' AND RDB$RELATION_NAME = 'T1';


In any case (whichever option you choose), make sure that the column doesn't have any NULLs. Firebird will not check it for you. Later when you backup the database, everything is fine, but restore will fail as the NOT NULL column has NULLs in it. To be safe, each time you change from NULL to NOT NULL, run a query to fill in eventual NULLs with some default value:

UPDATE T1 set C1 = 0 where C1 IS NULL;


If you occasionally created backup with wrong NOT NULL constraint and it seems like it cannot be restored, use gbak switch -NO_VALIDITY to turn off validity constraints and restore such backup. When backup is restored, simply populate the field using a statement like:

UPDATE T1 set C1 = 0 where C1 IS NULL;