> "TAX" FLOAT CHECK ("TAX" >= 0 AND "TAX" <= 0.1),
>
> The problem is that I can't insert a row that has a NULL "TAX" field
> because I get a check error (specifically "Operation violates CHECK
> constraint INTEG_25 on view or table COUNTY"). I thought I understood it
> that any operation involving a NULL value has an unknown result and that
> a check constraint only watches for a FALSE result so a NULL should be
> allowed here shouldn't it?

You are absolutely right when speaking about SQL standard,
unfortunately Firebird implements Check constraints in a non standard way,
such that both FALSE and NULL are rejected.
You have to enable nulls explicitely:

TAX FLOAT CHECK (TAX >= 0 AND TAX <= 0.1 OR TAX IS NULL),
or
TAX FLOAT CHECK (TAX BETWEEN 0 AND 0.1 OR TAX IS NULL),


Ivan Prenosil
[ I am looking for a job: InterBase - Firebird - Delphi - C - fulltext db - and more ... ]
Ivan.Prenosil@...
http://www.volny.cz/iprenosil/interbase

----- Original Message -----
From: "Scott Bussinger" <scottb@...>
Subject: [ib-support] Check constraint and null question


> Please help a newbie to SQL out and explain something for me. I'm trying
> to migrate an application written using Paradox files over to Firebird.
> I've got 1.5 RC2 loaded in a Win98 box and I'm using the Interbase
> DataPump and Database Desktop to play with everything and move some
> sample data across.
>
> The problem is that I have a table defined like this:
>
> CREATE TABLE "COUNTY" (
> "COUNTY" VARCHAR(15) NOT NULL,
> "STATE" VARCHAR(2) NOT NULL,
> "NAME" VARCHAR(20) NOT NULL,
> "REGION" VARCHAR(9) NOT NULL,
> "TAX" FLOAT CHECK ("TAX" >= 0 AND "TAX" <= 0.1),
> "NORTHEAST" CHAR(1),
> "NORTHWEST" CHAR(1),
> "SOUTHEAST" CHAR(1),
> "SOUTHWEST" CHAR(1)
> );
>
> The problem is that I can't insert a row that has a NULL "TAX" field
> because I get a check error (specifically "Operation violates CHECK
> constraint INTEG_25 on view or table COUNTY"). I thought I understood it
> that any operation involving a NULL value has an unknown result and that
> a check constraint only watches for a FALSE result so a NULL should be
> allowed here shouldn't it?