At 10:20 PM 4/06/2003 -0700, you wrote:

>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?

Not correct. NULL is not a value, it is a state. Your check constraint
CONSTRAINS any data in that column to be a value, i.e. non-null. It
further constrains the value of the data to be within your two bounds.

To allow NULL, you need to alter the constraint to

CHECK (value is null or value between 0.00 AND 0.10)

btw, before you go much further, can I suggest some things?
1) Avoid using quoted identifiers for db objects except in places where
you actually need to.
2) Don't use the FLOAT type for things that are going to be used in
calculations (such as a tax rate). You'll lose control of the precision in
multiplication and division operations. Use a scaled type, NUMERIC(2,2)
here, I think, since your constraint allows numbers which are smaller than
0.10.

Be careful about floating-point numbers (FLOAT and DOUBLE PRECISION). The
maxim is to use them only for "things you measure"; and even think those
cases through if they are ever going to be multiplied or divided.

3) If those CHAR(1) columns are Booleans (= Paradox type Logical), why not
define a Boolean domain? (But don't name it BOOLEAN, because true booleans
are coming in Fb 2...)

heLen