Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: The BOOLEAN data type

Re: The BOOLEAN data type

From: Troels Arvin <troels_at_arvin.dk>
Date: Tue, 01 Apr 2003 16:00:43 +0200
Message-ID: <pan.2003.04.01.14.00.42.519742@arvin.dk>


On Mon, 31 Mar 2003 10:11:15 -0800, --CELKO-- wrote:

> It gets to be a mess really fast.

As I understand you, the primary reason for it being messy is the eternal NULL-problem.

But then, making it optional in SQL:1999, was that perhaps a compromise?

> Aside from the destruction of the foundations of SQL, Booleans are
> usually, but not always, a sign of bad programming.

OK; good to think about.

Still, I think that there are many conceivable relations were true/false/unknown makes good sense. So my question then becomes "what's a good, cross-DBMS-product way of doing it; it there perhaps a de-facto standard for doing it?". The ways I see:

  1. As a boolean field
    Fails in important SQL implemetations like Oracle and MSSQL. Works in PostgreSQL. Fails in Mimer SQL.
  2. As a one-char bit string
    Fails in Oracle and Mimer SQL.

Works differently between PostgreSQL and MSSQL:

MSSQL: troels_at_jtds:windows> create table test(id int not null primary key, truth bit);
  affected 0 rows (6 msec)
troels_at_jtds:windows> insert into test values(42,0);   affected 1 rows (7 msec)
troels_at_jtds:windows> insert into test values(43,1);   affected 1 rows (2 msec)
troels_at_jtds:windows> insert into test values(44,B'1');   FAILURE: The name 'B' is not permitted in this context. Only   constants, expressions, or variables allowed here. Column   names are not permitted.
troels_at_jtds:windows> select * from test; ----+-------+
 id | truth |
----+-------+
 42 | false |
 43 | true |
----+-------+
2 rows in result (first row: 7 msec; total: 8 msec)

PostgreSQL:

troels=> create table test(id int not null primary key,truth bit);  NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index  'test_pkey' for table 'test'
CREATE TABLE
 troels=> insert into test values(42,0);  ERROR: column "truth" is of type bit but expression is of type  integer
 You will need to rewrite or cast the expression troels=> insert into test values(43,1);
 ERROR: column "truth" is of type bit but expression is of type  integer
 You will need to rewrite or cast the expression troels=> insert into test values(44,B'1'); INSERT 19180 1 troels=> select * from test;
 id | truth
----+-------
 44 | 1
(1 row)

c) As a magic char/value
In any database that I know of (except for MySQL[1]), one may use an ordinary type like char(1) or int combined with a CHECK in order to restrict the domain of values.

The portability of this solution makes it nice, but magic values are a bad habit in my opinion, primarily because of the lack consensus this results in (is it 0 or 'F' or 'f' or 'FALSE' or 'false' or 'n' or 'no' or 'N' or 'NO', ...).

I wonder if DB2 supports the boolean or bit types.

/Troels

Note 1: In MySQL, one may use ENUMs to achieve the same situation, but it ties you to MySQL which makes it a bad choise. Received on Tue Apr 01 2003 - 08:00:43 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US