Re: The BOOLEAN data type
Date: Tue, 01 Apr 2003 16:00:43 +0200
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:
- As a boolean field
Fails in important SQL implemetations like Oracle and MSSQL. Works in PostgreSQL. Fails in Mimer SQL.
- As a one-char bit string
Fails in Oracle and Mimer SQL.
Works differently between PostgreSQL and MSSQL:
troels_at_jtds:windows> create table test(id int not null primary key, truth
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)
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'
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
c) As a magic char/value
In any database that I know of (except for MySQL), 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', ...).
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 - 16:00:43 CEST