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 | +-------------+--------------+------+-----+---------+----------------+…

Accepted Answer

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

django unique together does not work with none datetime

Solution

Problem

Related problems