I see what you're saying, but I think the onus is on the user to make sure
things are being done correctly.
mysql>
mysql> use test;
Database changed
mysql>
mysql> # what's the current value for the SQL MODE ?
mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0.00 sec)
mysql>
mysql> drop table if exists nn_test;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> create table nn_test (
-> the_string varchar(25) NOT NULL DEFAULT "nada");
Query OK, 0 rows affected (0.08 sec)
mysql>
mysql> insert into nn_test values
-> (""),
-> (NULL),
-> (DEFAULT),
-> ("hello");
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 1
mysql>
mysql> show warnings;
+---------+------+------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------+
| Warning | 1048 | Column 'the_string' cannot be null |
+---------+------+------------------------------------+
1 row in set (0.00 sec)
mysql> show errors;
Empty set (0.00 sec)
mysql>
mysql> select * from nn_test;
+------------+
| the_string |
+------------+
| |
| |
| nada |
| hello |
+------------+
4 rows in set (0.00 sec)
mysql>
mysql> notee
From the MySQL Manual
http://dev.mysql.com/doc/#refman
Chapter 1.8.6.2. Constraints on Invalid Data
The reason for using the preceding rules in non-strict mode is that we can't
check these conditions until the statement has begun executing. We can't just
roll back if we encounter a problem after updating a few rows, because the
storage engine may not support rollback. The option of terminating the
statement is not that good; in this case, the update would be “half done,”
which is probably the worst possible scenario. In this case, it's better to
“do the best you can” and then continue as if nothing happened.
>
Thanks, Jay