Django唯一约束不能正常工作

Question

我有一个具有自定义_id的模型，它必须是唯一的，并且软删除、删除的对象不必具有唯一的_id，所以我这样做了：

class MyModel(models.Model):
    _id = models.CharField(max_length=255, db_index=True)
    event_code = models.CharField(max_length=1, blank=True, default='I')
    deleted = models.BooleanField(default=False)
    deleted_id = models.IntegerField(blank=True, null=True)

    objects = MyModelManager()   # manager that filters out deleted objects
    all_objects = MyModelBaseManager()    # manager that returns every object, including deleted ones

    class Meta:
        constraints = [
            UniqueConstraint(fields=['_id', 'event_code', 'deleted', 'deleted_id'], name='unique_id')
        ]

    def delete(self, *args, **kwargs):
        self.deleted = True
        self.deleted_id = self.max_related_deleted_id() + 1
        self.save()

    def undelete(self, *args, **kwargs):
        self.deleted = False
        self.deleted_id = None
        self.save()

    def max_related_deleted_id(self):
        # Get max deleted_id of deleted objects with the same _id
        max_deleted_id = MyModel.all_objects.filter(Q(_id=self._id) & ~Q(pk=self.pk) & Q(deleted=True)).aggregate(Max('deleted_id'))['deleted_id__max']
        return max_deleted_id if max_deleted_id is not None else 0

deleted_id的整个逻辑都在工作，我测试了它，问题是，UniqueConstraint不工作，例如：

$ MyModel.objects.create(_id='A', event_code='A')
$ MyModel.objects.create(_id='A', event_code='A')
$ MyModel.objects.create(_id='A', event_code='A')
$ MyModel.objects.filter(_id='A').values('pk', '_id', 'event_code', 'deleted', 'deleted_id')
[{'_id': 'A',
  'deleted': False,
  'deleted_id': None,
  'event_code': 'A',
  'pk': 1},
 {'_id': 'A',
  'deleted': False,
  'deleted_id': None,
  'event_code': 'A',
  'pk': 2},
 {'_id': 'A',
  'deleted': False,
  'deleted_id': None,
  'event_code': 'A',
  'pk': 3}]

下面是创建唯一约束的迁移：

$ python manage.py sqlmigrate myapp 0003
BEGIN;
--
-- Create constraint unique_id on model MyModel
--
ALTER TABLE `myapp_mymodel` ADD CONSTRAINT `unique_id` UNIQUE (`_id`, `event_code`, `deleted`, `deleted_id`);
COMMIT;

如有任何帮助，我们不胜感激！

Django版本= 2.2

Python版本= 3.7

数据库= MySQL 5.7

Answer 1

好了，我发现了我的问题，我会在这里发布这篇文章，以防有人遇到它：

问题出在MySQL，正如this post中所述，mysql允许在唯一约束中有多个null值，所以我不得不将deleted_id的默认值更改为0，现在它起作用了。