MySQL and not null is not good!

On a daily basis I continue to be unimpressed with MySQL when using MyISAM tables. Today I discovered that if you set a field to be non-nullable and then do an insert without specifying the field, MySQL happily completes the insert when I should be getting an error. Here’s an example:

create table test_state (
id bigint not null auto_increment,
date_created datetime not null,
state varchar(2) not null,
primary key (id)
);

insert into test_state (date_created) values (now());
insert into test_state (state) values ('WY');

select * from test_state
+----+---------------------+-------+
| id | date_created        | state |
+----+---------------------+-------+
|  1 | 2005-02-17 09:25:28 |       |
|  2 | 0000-00-00 00:00:00 | WY    |
+----+---------------------+-------+

You’ve got to be kidding me! It would be better if “not null” had no meaning and were simply ignored, whereas this confuses the issue even more. Now, I don’t profess to being a MySQL expert by any stretch and I know this is probably solved with InnoDB, but IMHO this shouldn’t work in MyISAM either! Oh how I miss the days of working with PostgreSQL, Oracle, and DB2.

That said I do enjoy the low administrative overhead of MySQL, it’s low memory footprint, and the excellent gnu readline and pipe support built into the client.