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.

This entry was posted in Database. Bookmark the permalink.

2 Responses to MySQL and not null is not good!

  1. Pingback: Todd Huss’ blog » Blog Archive » The time has come to upgrade to MySQL 5

  2. Pingback: When installing MySQL always set the sql-mode

Leave a Reply

Your email address will not be published. Required fields are marked *