List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 19 2002 5:47pm
Subject:re: BUG in InnoDB tables
View as plain text  
At 16:00 +0300 9/19/02, Victoria Reznichenko wrote:
>deep,
>Thursday, September 19, 2002, 2:28:07 PM, you wrote:
>
>dk> I am using MySQL 4.0.3 and Innodb tables for my
>dk> application,  I have created a unique index on
>dk> multiple column but its not working, here is sql
>dk> script to generate an bug
>
>
>dk> CREATE TABLE TESTING
>dk> (
>dk> ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY ,
>dk> FLD1 VARCHAR(100) NOT NULL ,
>dk> FLD2 VARCHAR(15) NOT NULL,
>dk> FLD3 INT NOT NULL ,
>dk> FLD4 INT NOT NULL ,
>dk> FLD5 INT NULL
>dk> )TYPE=InnoDB;
>
>dk> CREATE UNIQUE INDEX IX_FLD12345_TESTING ON TESTING
>dk> (FLD1,FLD2,FLD3,FLD4,FLD5);
>
>dk> INSERT INTO TESTING (FLD1,FLD2,FLD3,FLD4,FLD5)
>dk> VALUES("A","B",1,2,NULL), ("A","B",1,2,NULL);
>
>dk> Above INSERT statment tries to insert duplicate
>dk> records and though UNIQUE INDEX is created it allows
>dk> it.
>dk> Problem is with NULL value, if any of the index field
>dk> contains NULL than only this bug is generated.
>
>dk> Is it BUG or Behaviour?
>
>It's not a bug, it's a behaviour feature. UNIQUE can has multiple NULL values.

True for MyISAM and InnoDB tables.  If it would be acceptable to use a
BDB table instead, BDB tables do not allow multiple NULL values in UNIQUE
indexes.


(and what about ISAM tables? Question doesn't apply: indexed columns
must be NOT NULL for ISAM.)
Thread
BUG in InnoDB tablesdeep kapasi19 Sep
  • re: BUG in InnoDB tablesVictoria Reznichenko19 Sep
    • re: BUG in InnoDB tablesPaul DuBois19 Sep