Subject Re: [firebird-support] Strange validation error
Author Thomas Steinmaurer
Hi Eyal,

> I have the following domain:
> dom_input_code CHAR(1) CHECK (VALUE IN ('C', 'D'))
> 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.
> However when I try to insert a new row where the value of that column
> is NULL, I get a validation error. Similarly when I try to set the
> value of the column to NULL I get a validation error.
> I tried to drop the column, and re-add it using a simple type (ie. not
> the domain. I could add/modify rows and set the column value to NULL
> without any problem.
> Then I modified the column to use the domain - and again NULLs could
> not be inserted/update for the column. I checked and re-checked and
> neither the domain nor the column were defined as NOT NULL.
> I use FB 1.5.2 SuperServer and Database Workbench 2.8.5 as development
> front-end.
> Is this a bug in Firebird? Is this a "feature" of domains? Is there
> any workaround?

This behaviour is there from the beginning. I don't know if this is
compliant to the SQL standard, but the following works:

dom_input_code CHAR(1) CHECK ((VALUE IS NULL) OR (VALUE IN ('C', 'D')))

Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions