dimanche 27 février 2011

Contraintes uniques et valeurs nulles

Vous risquez d'être surpris si vous utiliser une contrainte d'unicité sur un groupe de colonnes qui peuvent être nulles. En effet, il faut savoir que la contrainte d'unicité différencie par défaut les valeurs nulles. Ainsi si vous créez la table suivante :

mysql> CREATE TABLE t1(id1 int,id2 int,id3 int);
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE t1 ADD UNIQUE KEY (id1,id2,id3);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

Vous pourrez ajouter les triplets (1,1,1), (1,2,1), (2,1,1) une seule fois. Par contre, vous pourrez ajouter autant de fois que vous le voulez les triplets (1,1,NULL), (1,NULL,1), (1,NULL,NULL), (NULL,NULL,NULL) etc...

mysql> INSERT INTO t1 VALUES(1,1,1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(1,2,1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(2,1,1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(2,1,1);
ERROR 1062 (23000): Duplicate entry '2-1-1' for key 'id1'

mysql> INSERT INTO t1 VALUES(1,1,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(1,1,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(1,NULL,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(1,NULL,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(NULL,NULL,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(NULL,NULL,NULL);
Query OK, 1 row affected (0.00 sec)

Apparemment, MySQL respecte le SQL 2003 qui spécifie que les contraintes d'unicité ne s'appliquent que sur les valeurs non nulles, ce qui ne semble pas très intuitif quand on choisi ce type de contrainte. Oracle ne prend pas en compte cette considération et vérfie l'unicité sur l'ensemble des valeurs, ce que nombre de personnes auraient certainement voulu retrouver chez MySQL...

Pour plus d'information, vous pouvez lire le bug report 25544