List:General Discussion« Previous MessageNext Message »
From:Dan Buettner Date:October 6 2006 2:53pm
Subject:Re: Bug or No bug - Composite Unique Key using null values
View as plain text  
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 <dan.julson@stripped> 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/mysql?unsub=1
>
>
Thread
Bug or No bug - Composite Unique Key using null valuesDan Julson6 Oct
  • Re: Bug or No bug - Composite Unique Key using null valuesDan Buettner6 Oct
  • Re: Bug or No bug - Composite Unique Key using null valuesMartijn Tonies6 Oct