django unique together does not work with none datetime

Question

I am using django 1.5.5 and python 2.7 and MySQL

this is my model

class Foo(models.Model):
     user = models.ForeignKey(User)
     date = models.DateTimeField(null=True, blank=True,editable=True)

     class Meta:
         unique_together = ('user', 'date')

If i use this i can add 2 records with the same user and an empty date.
I would like the uniqueness to be enforced even for empty date.

table create command

CREATE TABLE `management_foo` 
(  `id` int(11) NOT NULL AUTO_INCREMENT,  
   `user_id` int(11) NOT NULL, 
   `date` datetime DEFAULT NULL,  
    PRIMARY KEY (`id`),  
    UNIQUE KEY `user_id` (`user_id`,`date`),  
    KEY `management_foo_6232c63c` (`user_id`),  
    CONSTRAINT `user_id_refs_id_d47e5747` FOREIGN KEY (`user_id`) 
    REFERENCES `auth_user` (`id`)) 
    ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

and table describe

+-------------+--------------+------+-----+---------+----------------+  
| Field       | Type         | Null | Key | Default | Extra          |  
+-------------+--------------+------+-----+---------+----------------+  
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |   
| user_id     | int(11)      | NO   | MUL | NULL    |                |   
| date        | datetime     | YES  |     | NULL    |                |  
+-------------+--------------+------+-----+---------+----------------+

@yossi The answer I linked to is about PostgreSQL, but `null != null` is a feature of SQL not just PostgreSQL, so the answer applies to InnoDB as well. — Alasdair, Dec 02 '13 at 15:39

score 6 · Accepted Answer · answered Dec 02 '13 at 14:07

In InnoDB each NULL treats as unique value.

example:

mysql> create table x (i int, j int, primary key (i), unique key (j));
mysql> insert into x (i,j) values (NULL,NULL);
ERROR 1048 (23000): Column 'i' cannot be null
mysql> insert into x (i,j) values (1,NULL);
mysql> insert into x (i,j) values (2,NULL);
mysql> insert into x (i,j) values (3,3);
mysql> select * from x;
+---+------+
| i | j    |
+---+------+
| 1 | NULL |
| 2 | NULL |
| 3 |    3 |
+---+------+
3 rows in set (0.01 sec)

mysql> insert into x (i,j) values (4,3);
ERROR 1062 (23000): Duplicate entry '3' for key 'j'

You need to add not null constraint <=> remove null=True from field definition (replace it with default for example)

BTW, it's better to write in such style: unique_together = (("field1", "field2"),) – it will be much easier to extend unique pairs

score 3 · Answer 2 · answered Dec 02 '13 at 14:18

3

Just for the record: the SQL standard states that NULL is not a value and so must not be taken in account for unique constraints. IOW it has nothing to do with Django and actually is the expected behavior.

For a technical solution see akaRem's answer.

answered Dec 02 '13 at 14:18

bruno desthuilliers

75,974
6
88
118

django unique together does not work with none datetime

2 Answers2