Re: The BOOLEAN data type

71062.1056_at_compuserve.com (--CELKO--) wrote in message news:<c0d87ec0.0303311011.2966d628_at_posting.google.com>...
> No, NULL is not the same as UNKNOWN and zero is not the same the empty
> string.

So what do we put in a NUMBER column when the value is unknown, if not NULL - we sure don't put a zero there. As far as I ever knew, logic only has TRUE and FALSE values. SQL has made a third option, NULL, available - now you are saying we need 4 options (I won't say "values"!) What would the semantic difference between NULL and UNKNOWN be?

> There were a few reason for not having it in SQL-92.
>
> To be a datatype in SQL, you have to be NULL-able. If you allow a
> LOGICAL datatype, then it has to be TRUE, FALSE, UNKNOWN and be
> NULL-able! But NULL does not work with AND, OR and NOT. Then there
> is the problem that an empty scalar subquery is cast as a NULL. The
> CHECK() constraint in the DDL accepts predicatres that test UNKNOWN,
> while ON and WHERE in the DML do not. It gets to be a mess really
> fast.

Yes, I can see there would be problems.

> Aside from the destruction of the foundations of SQL, Booleans are
> usually, but not always, a sign of bad programming. Someone is
> storing the state of the database at one point in time as a flag. It
> is a computed column and we all know better than to store redundant
> data like that.

I would say "Booleans are sometimes, but not usually, a sign of bad programming". Certainly I would say that using BOOLEAN to represent gender (as someone else suggested) is a very bad idea. But in reality, there are hundreds of well-defined columns in properly designed databases that record a truth value of some kind, and because of the absence of a BOOLEAN datatype have to use a kludge like (1,0) or ('Y','N'), with the addition of a check constraint required (NB I've never seen anyone add a third value like -1 or 'U' to represent UNKNOWN, they'd leave it NULL). I don't think we should be denied a useful datatype on the grounds that we're not grown up enough to use it properly. Bad designers will just go ahead and misuse a NUMBER or VARCHAR column instead anyway. Received on Tue Apr 01 2003 - 11:50:21 CEST