FirebirdSQL logo

Adding a CHECK constraint to an existing column

To add a CHECK constraint to a column, use one of the following syntaxes:

alter table Stk add check (Amt is not null)
alter table Stk add constraint AmtNotNull check (Amt is not null)

The second form is preferred because it gives you an easy handle to drop the check, but the constraints themselves function exactly the same.As you may have expected, existing NULLs in the column will remain, can be backed up and restored, etc.etc. — see [nullguide-add-check-not-null-field].

Removing a NOT NULL constraint

If you gave the NOT NULL constraint a name when you created it, you can simply drop it:

alter table Adventures drop constraint IdNotNull

If you forgot the name, you can retrieve it with isql’s `SHOW TABLE command (i.c. SHOW TABLE ADVENTURES);other clients may have their own provisions to let you find or browse constraint names.

If you didn’t name the constraint explicitly, Firebird has created a name for it, but SHOW TABLE won’t display it.You have to use this piece of SQL to dig it up:

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>'

Don’t break your head over some of the table and field names in this statement;they are illogical but correct.Make sure to uppercase the names of your table and field if they were defined case-insensitively.Otherwise, match the case exactly but don’t enclose the names in double-quotes like you would do in a regular query.Also don’t include the angle brackets (<>). Once you have the constraint name, you can drop it just like in the previous example.

Tip

If the above statement returns an empty set and you are sure that you’ve correctly filled in the table and field names (including case!), and the constraint did not come from a domain either (this is discussed in the next sections), it may be that a third-party tool has made the column NOT NULL by setting a flag in a system table.In that case it’s probably best to remove it again with the same tool.If that is not an option, check the field’s NULL flag with:

select rdb$null_flag from rdb$relation_fields
where  rdb$relation_name  = '<TableName>'
       and rdb$field_name = '<FieldName>'

If the flag is NULL or 0, the field is nullable (at least as far as this flag is concerned).If it’s 1, clear it with:

update rdb$relation_fields set rdb$null_flag = null    /* or 0 */
where  rdb$relation_name  = '<TableName>'
       and rdb$field_name = '<FieldName>'

followed by a commit.

Important

As soon as you’ve dropped the NOT NULL constraint — by whichever method — SHOW TABLE will report the column as nullable.Any existing NULLs that were previously illegal and therefore hidden by most clients (see [nullguide-nulls-reported-as-zeroes]) will become visible again.

However, before you can insert NULLs into the column, you must commit your work, close all connections to the database, and reconnect.

Removing a domain-based NOT NULL constraint

If the NOT NULL constraint came with a domain, it is not registered directly with the column.This means you can’t DROP it from the column either.Instead, change the column’s type to a nullable domain or built-in data type:

alter table MyTable alter MyColumn type int

Even though this time the constraint was not tied directly to the column, you must again close all connections and reconnect before NULL input is accepted.