Making NOT NULL columns nullable again in Firebird

I came across the need to make a NOT NULL column nullable again recently, and was somewhat suprised to find that it's actually a little harder than you might think in Firebird SQL.

take the following ficticious example:

CREATE TABLE pointless
(
    widget VARCHAR(40) NOT NULL
);

now, in, say Oracle, I would simply do this:

ALTER TABLE pointless MODIFY COLUMN widget VARCHAR(40);

et voila !!

Turns out you can't do that in Firebird, so I had to do a little digging. It turns out that Firebird internally handles NOT NULL columns as constraints, and you have to drop the constraint to remove the not null clause.

The full explanation of what to do, and why is here: http://www.firebirdsql.org/manual/nullguide-alter-pop-tables.html#nullguide-make-column-nullable but for those of you masses that just can't wait, try this:

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'

Substituting the table and column names as appropriate. The resulting row will give you the internal constraint name that you can simply drop as follows:

ALTER TABLE tablename DROP CONSTRAINT constraintname

Job done !