with MySQL 3.23.33 on Linux, shouldn't UNIQUE columns that contain NULL
values still reject rows that are the same? this isn't working (it allows
duplicate rows to be included if one of the columns is NULL). this seems wrong.
here's an example:
mysql> create table n (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
a VARCHAR(10) NOT NULL,
b VARCHAR(10) NULL,
c VARCHAR(10) NULL,
UNIQUE(a, b, c)
);
Query OK, 0 rows affected (0.00 sec)
mysql> describe n;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| a | varchar(10) | | MUL | | |
| b | varchar(10) | YES | | NULL | |
| c | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into n values(NULL, 'a', 'b', 'c');
Query OK, 1 row affected (0.00 sec)
mysql> insert into n values(NULL, 'a', 'b', 'c');
ERROR 1062: Duplicate entry 'a-b-c' for key 2
perfect
mysql> insert into n values(NULL, 'a', 'b', NULL);
Query OK, 1 row affected (0.00 sec)
mysql> insert into n values(NULL, 'a', 'b', NULL);
Query OK, 1 row affected (0.00 sec)
why is this allowed?
mysql> select * from n;
+----+---+------+------+
| id | a | b | c |
+----+---+------+------+
| 1 | a | b | c |
| 2 | a | b | NULL |
| 3 | a | b | NULL |
+----+---+------+------+
3 rows in set (0.00 sec)
mysql> insert into n values(NULL, 'a', NULL, 'c');
Query OK, 1 row affected (0.00 sec)
mysql> insert into n values(NULL, 'a', NULL, 'c');
Query OK, 1 row affected (0.00 sec)
why is this allowed?
mysql> select * from n;
+----+---+------+------+
| id | a | b | c |
+----+---+------+------+
| 1 | a | b | c |
| 2 | a | b | NULL |
| 3 | a | b | NULL |
| 4 | a | NULL | c |
| 5 | a | NULL | c |
+----+---+------+------+
5 rows in set (0.00 sec)
did i make a mistake or misunderstand something? or is this a bug?
john
---------------------------------------------------------------------
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
