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.)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
