* Donal Murtagh
> My DB consists of INNODB tables, such as...
>
>
> CREATE TABLE user
> (
> name VARCHAR(255) NOT NULL
>
> ) TYPE = INNODB;
>
>
> The NOT NULL qualifier doesn't have the effect I expected. Although I
> can't do this...
>
> INSERT user VALUES (null);
>
>
> I can do this:
>
> INSERT user VALUES ("");
>
>
> I appreciate that one could argue "" is different to NULL and
> therefore there is nothing wrong with this behaviour.
Right.
> However, surely there is no defending the following (which is also
> allowed):
>
> UPDATE user SET name = NULL;
I understand why you are suprised, but this is normal behaviour for mysql.
In general, if an illegal value is inserted, the 'best fit' is used. In this
case, the string "" is considered to be the 'best fit' for the illegal NULL.
For integers, you would get the smallest or highest possible value or zero:
mysql> use test
Database changed
mysql> create table zx1 (id tinyint not null);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into zx1 values (NULL),(''),(-200),(200);
Query OK, 4 rows affected (0.09 sec)
Records: 4 Duplicates: 0 Warnings: 4
mysql> select * from zx1;
+------+
| id |
+------+
| 0 |
| 0 |
| -128 |
| 127 |
+------+
4 rows in set (0.06 sec)
For strings you get the empty string "" when NULL is inserted but not
allowed.
> Is there any way I can prevent a field from being set to NULL or "" (or
> even just NULL)? I am using version 4.0.17-nt
When a column is defined as NOT NULL, it can not contain NULL, but it may
still contain the empty string "" (or 0 for numeric columns).
The only way I can think of to prevent insertion of empty strings in mysql
would be to have an unique index and allready having a row with an empty
value. Then you would get an duplicate entry error:
mysql> create table notblank (c varchar(255) not null primary key);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into notblank values (""),("a"),("b");
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> update notblank set c="" where c="a";
ERROR 1062: Duplicate entry '' for key 1
mysql> update notblank set c=NULL where c="a";
ERROR 1062: Duplicate entry '' for key 1
Note that the last statement is actually trying to update the column to "",
not to NULL.
The reason for this behaviour is to get better performance. There is no
attribute constraint checking in mysql, leaving more work to the developers,
but the server is running faster. We can stand a little extra work if it
runs extra fast, right? :)
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
