Firebird Documentation Index → Firebird Null Guide → 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.
Firebird Documentation Index → Firebird Null Guide → CHECK constraints |