> When i check for "IN (null, ...)" in a check constraint, it allows to store any value in the field.
> I presume the cause is the IN behavior when handling nulls. Is it incorrect to use NULLs inside INs?
>
> Regards,
>
> Fabiano
>
> FB 2.1.2 CS, Windows
>
> create domain boolean1 as
> char(1) character set iso8859_1 check (value in (NULL, 'S', 'N')) collate iso8859_1;
>
> create domain boolean2 as
> char(1) character set iso8859_1 check (value in ('S', 'N')) collate iso8859_1;
>
> create table test1 (
> field1 integer not null,
> field2 boolean1);
>
> create table test2 (
> field1 integer not null,
> field2 boolean2);
>
> insert into test1 values (1, null);
> -- OK
> insert into test1 values (1, '?');
> -- OK
> insert into test1 values (1, 'S');
> -- OK
>
> insert into test2 values (1, null);
> -- OK
> insert into test2 values (1, '?');
> -- ERROR
> insert into test2 values (1, 'S');
> -- ERROR
>
> select * from test1 a where a.field2 in (null, 'S', 'N');
> FIELD1 FIELD2
> 1 S

http://www.firebirdsql.org/manual/nullguide-check-constraints.html


--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com
My blog:
http://blog.upscene.com/thomas/