Dan, this is documented behavior:
http://dev.mysql.com/doc/refman/5.0/en/create-index.html
Specifically,
"A UNIQUE index creates a constraint such that all values in the index
must be distinct. An error occurs if you try to add a new row with a
key value that matches an existing row. This constraint does not apply
to NULL values except for the BDB storage engine. For other engines, a
UNIQUE index allows multiple NULL values for columns that can contain
NULL."
Dan
On 10/6/06, Dan Julson <[EMAIL PROTECTED]> wrote:
List,
I have been mulling over this for a few days reading docs and going back and
forth with people on this, so I figured I would come here before writing up a
bug report.
First of all, I have tested this on 4.1.18, 5.0.16, and 5.0.22 within Solaris
9 and Mandrake Linux LE 2005 environments.
For the sake of ease, I will just set up a small test table to assist me with
this question.
> Create table test (
x smallint not null,
y char(5) default null,
z char(10) not null default '',
Unique Key `s`(x, y)
) ENGINE=MyISAM
test> Insert into test values (1, 'dan', 'yes'), (2, 'joe', 'no'), (3, NULL,
'maybe');
test> Select * from test;
+---+------+-------+
| x | y | z |
+---+------+-------+
| 1 | dan | yes |
| 2 | joe | no |
| 3 | NULL | maybe |
+---+------+-------+
Now, here is the kicker. When I try to run an Insert Into test Values (3,
NULL, 'yes'). It inserts it without matching the duplicate key which clearly
is in the table, or is it. As the docs state, NULL can never equal NULL, so
this is the correct behavior. Which leads me to my question...
test> Insert into test Values (3, NULL, 'yes');
Query OK, 1 row affected (0.00 sec)
test> Select * from test;
+---+------+-------+
| x | y | z |
+---+------+-------+
| 1 | dan | yes |
| 2 | joe | no |
| 3 | NULL | maybe |
| 3 | NULL | yes |
+---+------+-------+
Should the duplicate key checker be using the null-safe equals operator when
checking for duplicate unique keys? Since primary keys cannot have nulls in
them, then they are fine. This only happens when a unique key with a null
value is encountered.
Thanks, in advance, for any and all input.
--
-Dan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
