Friday, April 17, 2009

UNIQUE Index Insanity

Does this look right to you?  Oracle doesn't work this way.  But MySql insists this is the desirable behavior.





mysql> create table foobar (x int, y int, unique(x, y));
Query OK, 0 rows affected (0.10 sec)

mysql> insert into foobar values (1, null);
Query OK, 1 row affected (0.00 sec)

mysql> insert into foobar values (1, null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from foobar;
+------+------+
| x | y |
+------+------+
| 1 | NULL |
| 1 | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.33 |
+-----------+
1 row in set (0.00 sec)

mysql>