I am experiencing odd behavior, and I'm hoping someone can tell me if
I'm doing something wrong or 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 update still
completes successfully, but transforms the NULL value to an empty
string.
The preferred behavior would be for mysql to emit an error and reject
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 than 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 updates of a NOT NULL field
with a NULL value 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.
--
Steven Hilton <[EMAIL PROTECTED]>
<http://mshiltonj.com/>
"It is the duty
of the patriot
to protect his country
from its government."
-- Paine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
