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

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;
+---+
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

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