Subject Re: Simulating Boolean field
Author Adam
--- In firebird-support@yahoogroups.com, "Bc. Jiri Cincura"
<diskuze@c...> wrote:
> Hi,
>
> today morning I was thinking about the Boolean simulation in FB 1.5
. I've
> found two solutions.
> 1: char(1) with 'T' and 'F' and some validation (check, trigger)
> 2: char(1) with X (as Yes/True) and NULL (No/False)
>
> What's better (performance, management, ...)?

Your first option is better by far, see others' comments regarding
your incorrect use of NULL.

> How you're working with Boolean?

We are using two domains depending on the need

CREATE DOMAIN "BOOLCHAR" AS CHAR(1)
check( (value in ('T','F')) or value IS NULL);

CREATE DOMAIN "NNBOOLCHAR" AS CHAR(1)
default 'F'
check(value in ('T','F')) NOT NULL;

Use the first one if you want to allow NULLs, the second if you don't.
Of course you could make a third one that doesn't default if you
really wanted, but these two options do us well.

Case sensitivity is not an issue, it does not accept anything other
than 'T' or 'F'.

A simple function converts BoolCharToBoolean and vice versa. You can
even trick it (in Delphi) by creating the field as a TBooleanField,
and it will populate correctly.

You could just as easily do this with 0 and 1 (you would still need
the check constraint to make sure I didn't store 2 in there).

Adam