Search code examples
sqlfirebirdfirebird2.5

How to remove null constraint for a column in Firebird 2.5

How to remove not null constraint for a column in Firebird 2.5?

Sadly, this didn't work:

Alter table ESTOQUE_GRUPO_SUBELEMENTO ALTER column ID_SUBELEMENTO  TYPE int  null  ;
Solution

  • In Firebird 2.5, you cannot use DDL to add a NOT NULL constraint to an existing column, and dropping is only possible indirectly by dropping the constraint by name. This was changed in Firebird 3.0 (see Manage Nullability in Domains and Columns in the Firebird 3.0 release notes).

    A NOT NULL is implemented as a constraint. If you named it (or lookup the generated name in the system tables), you can drop the constraint in Firebird 2.5. As documented in the Firebird Null Guide, section Making non-nullable columns nullable again.

    To drop it:

    alter table Adventures drop constraint IdNotNull

    (where IdNotNull is the name of the NOT NULL constraint)

    To find the name of a constraint, you can use the following query:

    select rc.rdb$constraint_name
from rdb$relation_constraints rc
inner 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>'

    See the link above for further details.

    In Firebird 3.0 and higher, you can also use

    alter table table_name alter column_name drop not null