|Subject||Re: [firebird-support] Strange validation error|
>> > dom_input_code CHAR(1) CHECK (VALUE IN ('C', 'D'))Is it intention that the behaviour is different for domain vs. field ? Try this:
>> > I have a table with the input_code column that uses the domain as the
>> > data type. Neither the domain nor the table DDL forbid NULL in that
>> > column, ie. NULL should be allowed.
> At 02:04 AM 2/05/2006, Ann wrote:
>>NULL is not either 'C' or 'D'. The behavior you describe is correct
>>and follows the standard. You'd see the same behavior if you put
>>the constraint on the column rather than the domain. If you want
>>to allow nulls, you must explicitly allow them in the constraint.
> Actually, it seems our behaviour complies with an old standard but
> not the current one. According to Paul Ruizendaal (I think it was he
> who raised it in the tracker), if a CHECK constraint is placed on a
> nullable column, then NULL is allowed, i.e. we are wrong (in <= Fb
> 1.5) to require NULL to be explicitly allowed.
> In Fb 2.0 the rule was changed to comply with the newer standard --
> see P. 90 of the release notes (or P. 99 if you are using Acrobat
> page numbers).
CREATE DOMAIN D INTEGER CHECK(VALUE > 10);
CREATE TABLE T (
A INTEGER CHECK(A > 10),
This insert will suceed:
INSERT INTO T(A,B) VALUES(NULL, 20);
This one will fail:
INSERT INTO T(A,B) VALUES(20, NULL)
Statement failed, SQLCODE = -625
validation error for column B, value "*** null ***"