<< Keys and unique indices | Firebird Null Guide | SELECT DISTINCT >>

CHECK constraints

It has been said several times in this guide that if test expressions return NULL, they have the same effect as false: the condition is not satisfied. Starting at Firebird 2, this is no longer true for the CHECK constraint. To comply with SQL standards, a CHECK is now passed if the condition resolves to NULL. Only an unambiguous false outcome will cause the input to be rejected.

In practice, this means that checks like

 check ( value > 10000 )

 check ( upper( value ) in ( 'A', 'B', 'X' ) )

 check ( value between 30 and 36 )

 check ( ColA <> ColB )

 check ( Town not like 'Amst%' )

...will reject NULL input in Firebird 1.5, but let it pass in Firebird 2. Existing database creation scripts will have to be carefully examined before being used under Firebird 2. If a domain or column has no NOT NULL constraint, and a CHECK constraint may resolve to NULL (which usually – but not exclusively – happens because the input is NULL), the script has to be adapted. You can extend your check constraints like this:

 check ( value > 10000 and value is not null )

 check ( Town not like 'Amst%' and Town is not null )

However, it's easier and clearer to add NOT NULL to the domain or column definition:

 create domain DCENSUS int not null check ( value > 10000 )

 create table MyPlaces
 (
   Town varchar(24) not null check ( Town not like 'Amst%' ),
   ...
 )

If your scripts and/or databases should function consistently under both old and new Firebird versions, make sure that no CHECK constraint can ever resolve to NULL. Add or ... is null if you want to allow NULL input in older versions. Add NOT NULL constraints or and ... is not null restrictions to disallow it explicitly in newer Firebird versions.

See also:
Constraints
CHECK constraint

back to top of page
<< Keys and unique indices | Firebird Null Guide | SELECT DISTINCT >>