I've lost too much hair over this one.

I'm trying to create a table that has a column that will ONLY accept
TRUE or FALSE (0 or 1).

If I set the type to BOOL, then it will accept any value that will fit
into a tinyint.

If I set the type to ENUM and only allow 0 or 1, MySQL insists on
allowing 3 values - NULL, 0, & 1, even if null is not allowed for the
column.

Any help would be greatly appreciated.

Lee

· Lee Peedin <lpeedinDONOTSPAME@nc.rr.com>:

> If I set the type to ENUM and only allow 0 or 1, MySQL insists on
> allowing 3 values - NULL, 0, & 1, even if null is not allowed for the
> column.
>
> Any help would be greatly appreciated.

Hm. You wan't a column, which can only take two values. With an
ENUM which allows 0 and 1, you've got *three* possible values:
0, 1 and '' (empty string) (BTW: NULL can be excluded by defining
the column with NOT NULL, which contradicts what you wrote).

To get this, I've got the following table:

mysql> describe booltest;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| b | enum('true') | NO | | | |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

In "b", I can only insert "true" or "" which gives
two possible values.

OTOH: What's bad about a plain tinyint field? In your program,
you could define, that 0 is false and anything which is not 0
is true. Then you've got your two possible values as well.

Reg. your NULL problem:

mysql> insert into booltest set b = not null;
ERROR 1048 (23000): Column 'b' cannot be null

Alexander Skwar
--
BOFH Excuse #309:

firewall needs cooling

> I'm trying to create a table that has a column that will ONLY accept
> TRUE or FALSE (0 or 1).
>
> If I set the type to BOOL, then it will accept any value that will fit
> into a tinyint.
>
> If I set the type to ENUM and only allow 0 or 1, MySQL insists on
> allowing 3 values - NULL, 0, & 1, even if null is not allowed for the
> column.

mysql> create table t(a enum('0','1') not null);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t values(3);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t;
+---+
| a |
+---+
| |
+---+
1 row in set (0.00 sec)

mysql> delete from t;
Query OK, 1 row affected (0.00 sec)

mysql> set sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(3);
ERROR 1265 (01000): Data truncated for column 'a' at row 1
mysql> select * from t;
Empty set (0.01 sec)



Regards
Dimitre

· Radoulov, Dimitre <dradoulov@_gmail.com>:

>> I'm trying to create a table that has a column that will ONLY accept
>> TRUE or FALSE (0 or 1).

> mysql> create table t(a enum('0','1') not null);
> Query OK, 0 rows affected (0.03 sec)
>
> mysql> insert into t values(3);
> Query OK, 1 row affected, 1 warning (0.00 sec)
>
> mysql> select * from t;
> +---+
> | a |
> +---+
> | |
> +---+

So your "a" column can take 3 values: 0, 1 and ''. OP wanted
to get only 2 values.

Alexander Skwar
--
Do not overtax your powers.

>> mysql> insert into t values(3);
>> Query OK, 1 row affected, 1 warning (0.00 sec)
>>
>> mysql> select * from t;
>> +---+
>> | a |
>> +---+
>> | |
>> +---+
>
> So your "a" column can take 3 values: 0, 1 and ''. OP wanted
> to get only 2 values.

It doesn't (after setting sql_mode to strict_all_tables).
However, it accepts the value 2.

So you can use views (see http://arjen-lentz.livejournal.com/49881.html).

mysql> create table t(a tinyint not null);
Query OK, 0 rows affected (0.03 sec)

mysql> set sql_mode=strict_all_tables;
Query OK, 0 rows affected (0.00 sec)

mysql> create or replace view t_view as select * from t where a between 0
and 1 WITH CASCADED CHECK OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_view values (0);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_view values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_view values (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.t_view'
mysql> insert into t_view values (NULL);
ERROR 1048 (23000): Column 'a' cannot be null
mysql> insert into t_view values ('');
ERROR 1366 (HY000): Incorrect integer value: '' for column 'a' at row 1



Regards
Dimitre