Enforcing NOT NULL restriction on UPDATE

[Steven Hilton]

I am experiencing odd behavior, and I'm hoping someone can explain why
it is behaving this way, and how to get around it...

When I update a row in a table with a field's data set to NULL, but
the table has a NOT NULL restriction on the field, the insert still
completes successfully, but transforms the NULL value to an empty
string.

The preferred behavior would be for mysql to emit and error and not
accept the attempt to set a NULL value for a NOT NULL field, and not
transform the data.

Sample sql to reproduce is below:

create table domain
(
-- define columns
id int(10) unsigned not null auto_increment,
name varchar(128) not null,

-- set primary key
primary key (id),

-- build indexes
unique key domain_name (name),

) type=innodb;
mysql> insert into domain (name) values (NULL);
ERROR 1048: Column 'name' cannot be null
-- This is desired behavior;

mysql> insert into domain (name) values ('google.com');
Query OK, 1 row affected (0.01 sec)

mysql> select * from domain;
+----+------------+
| id | name |
+----+------------+
| 57 | google.com |
+----+------------+
1 row in set (0.00 sec)

mysql> update domain set name = NULL where id = 57;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
-- update is accepted, why??
-- shouldn't it error??

mysql> select * from domain;
+----+------+
| id | name |
+----+------+
| 57 | |
+----+------+
1 row in set (0.01 sec)

mysql> select * from domain where name = '';
+----+------+
| id | name |
+----+------+
| 57 | |
+----+------+
1 row in set (0.00 sec)

mysql> select * from domain where name = NULL;
Empty set (0.00 sec)

For some reason, 'show create table domain' shows a different sql
statement that what I actually used to create the table, notably the
appended "default ''" on the definition of the name field:

CREATE TABLE `domain` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(128) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `domain_name` (`name`)
) TYPE=InnoDB

I'm not sure why mysql is allowing attempted inserts of a NULL value
on a NOT NULL field to succeed, or why it is altering the data being
sent in. Is there a way to suppress this behavior and have it be more
strict in what data it accepts for NOT NULL fields?

Thanks for any input.