MySQL Bugs: #9844: Unique Index constraint doesn't work

Bug #9844	Unique Index constraint doesn't work
Submitted:	12 Apr 2005 13:47	Modified:	15 Apr 2005 13:20
Reporter:	Sergi Vergés	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0.3-beta-nt	OS:	Windows (Windows 2000 sp4)
Assigned to:		CPU Architecture:	Any

Description:
I've a table with a primery key, and a constraint unique of two columns.
When I insert a record that violate the constraint the server doesn't raise me an error.
But with two identical records in the table, if i altered the table and add the constraint then the server raises me the error "ERROR 1061 (42000): Duplicate key name...".

How to repeat:
Create the table with the following script:
DROP TABLE TIPUS
;
CREATE TABLE TIPUS(
	TIPUS_ID INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
	MARCA VARCHAR(100),
	PREU FLOAT,
	STOCK INTEGER,
	DATA_BAIXA DATETIME
)
;
ALTER TABLE TIPUS
ADD CONSTRAINT TIPUS_UK UNIQUE
(
 MARCA,
 DATA_BAIXA
)
;

Then insert two recrods as:
insert into tipus(marca) values('m');
insert into tipus(marca) values('m');

No error raised.
Alter the table and add the constraint again:
ALTER TABLE TIPUS
ADD CONSTRAINT TIPUS_UK UNIQUE
(
 MARCA,
 DATA_BAIXA
)
;

Error raised!!!!

Anyway if I delete the created records, and try to alter the table to add another time de constraint
ALTER TABLE TIPUS_ALCOHOL
ADD CONSTRAINT TIPUS_ALCOHOL_UK UNIQUE
(
 MARCA,
 DATA_BAIXA
);
the server launch the error of duplicate keys, when there are no records.
Thanks

mysql> show create table TIPUS\G
*************************** 1. row ***************************
       Table: TIPUS
Create Table: CREATE TABLE `tipus` (
  `TIPUS_ID` int(11) NOT NULL auto_increment,
  `MARCA` varchar(100) default NULL,
  `PREU` float default NULL,
  `STOCK` int(11) default NULL,
  `DATA_BAIXA` datetime default NULL,
  PRIMARY KEY  (`TIPUS_ID`),
  UNIQUE KEY `TIPUS_UK` (`MARCA`,`DATA_BAIXA`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> insert into tipus(marca) values('m');
Query OK, 1 row affected (0.04 sec)

mysql> insert into tipus(marca) values('m');
Query OK, 1 row affected (0.04 sec)

mysql> select * from TIPUS;
+----------+-------+------+-------+------------+
| TIPUS_ID | MARCA | PREU | STOCK | DATA_BAIXA |
+----------+-------+------+-------+------------+
|        1 | m     | NULL |  NULL | NULL       |
|        2 | m     | NULL |  NULL | NULL       |
+----------+-------+------+-------+------------+
2 rows in set (0.00 sec)

Notice above that you didn't violate the unique constraint since
one of the columns can hold NULL values.

mysql> ALTER TABLE TIPUS
    -> ADD CONSTRAINT TIPUS_UK UNIQUE
    -> (
    ->  MARCA,
    ->  DATA_BAIXA
    -> )
    -> ;
ERROR 1061 (42000): Duplicate key name 'TIPUS_UK'

The above error is true TIPUS_UK already exists.

Sorry, I didn't notice what the error was saying.

I think that this behaviour is not correct at all, because the null value is a value too. 

This example was for save historical data, as dimension table in a datawarehouse. Marca is the name of an entity and Data_Baixa is the deletion_date. 

I couldn't have two records in the table with the same name and both active.

Thanks for all.

Sergi,
In SQL, NULL=anything does not hold. Because of this, a multi-column unique index will accept duplicates if any of the columns is NULL. I believe that this is specified in the SQL standard.