Hi Heikki, I have proven this in MySQL 3.23.47 and I have found a problem with "CHECK TABLE", that reports "error - corrupted table" if there are several NULL's in a UNIQUE index.
Happy end of year to all!, José Ceferino Ortega -----Mensaje original----- Hi! I will change InnoDB so that it allows several NULL values in secondary indexes even if that index would have been declared UNIQUE. Oracle has a similar convention. In the case of foreign keys, I advise against using NULL values, because in SQL NULL really means 'an unknown value'. Actually, since NULL means an 'unknown value' it would be logical that the SQL standard would ban the use of NULLs in any UNIQUE index. Currently it has only been banned in a primary key. Regards, Heikki Innobase Oy --- See http://www.innodb.com for the latest news about InnoDB Order commercial MySQL/InnoDB support at https://order.mysql.com/ >I have found a "problem" with UNIQUE in InnoDB. > >Example: > >mysql> CREATE TABLE b ( > -> idB int(11) NOT NULL auto increment, > -> valor int(11) default NULL, > -> PRIMARY KEY (idB), > -> UNIQUE KEY valor (valor) > -> ) TYPE=MyISAM; >Query OK, 0 rows affected (0.00 sec) > >mysql> INSERT INTO b (idB, valor) VALUES (1,1),(2,2),(3,NULL),(4,NULL); >Query OK, 4 rows affected (0.01 sec) >Registros: 4 Duplicados: 0 Peligros: 0 > >mysql> select * from b; >+-----+-------+ >| idB | valor | >+-----+-------+ >| 1 | 1 | >| 2 | 2 | >| 3 | NULL | >| 4 | NULL | >+-----+-------+ >4 rows in set (0.00 sec) > >mysql> alter table b type=InnoDB; >ERROR 1062: Entrada duplicada 'NULL' para la clave 2 > > >Why? I know that NULL != NULL, then behaviour in MyISAM is correct but >in InnoDB in not. > >With "foreign key constraints", I have found the same problem: I can't >insert a NULL in a column that "references" other. > >Thanks, >José Ceferino Ortega > > >----------------------------------------------------------------------- >---- ----- > --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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
