RE: UNIQUE in InnoDB
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
RE: UNIQUE in InnoDB
Jose, thank you for the bug report. You are right: if there are several NULLs in a unique secondary index, CHECK TABLE will report table corruption because of a duplicate key in the index. I forgot to ease the check in CHECK TABLE. I have now fixed this for 3.23.48. Regards, Heikki Innobase Oy .. mysql create table nulls (a int not null, b int, primary key (a), unique index (b)) type = innodb; Query OK, 0 rows affected (0.01 sec) mysql insert into nulls values (1, NULL); Query OK, 1 row affected (0.00 sec) mysql insert into nulls values (2, NULL); Query OK, 1 row affected (0.00 sec) mysql check table nulls; ++---+--+--+ | Table | Op| Msg_type | Msg_text | ++---+--+--+ | test.nulls | check | error| Corrupt | ++---+--+--+ 1 row in set (0.02 sec) mysql .. heikki@donna:~/mysqlm/sql mysqld 011231 14:05:28 InnoDB: Started mysqld: ready for connections Error: duplicate key in index b InnoDB: prev record 0: SQL NULL; 1: len 4; hex 8001; asc ;; InnoDB: record RECORD: info bits 0 0: SQL NULL; 1: len 4; hex 8002; asc ;; 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--- - 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
Re: UNIQUE in InnoDB
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
Re: UNIQUE in InnoDB
On Sun, Dec 16, 2001 at 09:33:06PM +0200, Heikki Tuuri wrote: 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'. Would it not make sense to not 'match' foreign NULL values at all? That is, to ignore them entirely when used as foreign keys, as though the row was not there? If, for example, CustID in one table must match Cust.ID in another and CustID is NULL (and the column allows this), would it be ignored or require a 'matching' NULL as a Cust.ID value? -- Michael T. Babcock CTO, FibreSpeed Ltd. (Hosting, Security, Consultation, Database, etc) http://www.fibrespeed.net/~mbabcock/ - 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
