Django unique constraint not working properly

Question

I have a model with a custom _id that has to be unique, and soft delete, deleted objects don't have to have a unique _id, so I did it as follows:

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

The whole logic of the deleted_id is working, I tested it out, the problem is, the UniqueConstraint is not working, for example:

$ 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}]

Here is the migration that created the unique constraint:

$ 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;

Any help is appreciated!

Django version = 2.2

Python version = 3.7

Database = MySQL 5.7

score 2 · Answer 1 · answered Jun 04 '20 at 15:43

2

Ok, I figured out my problem, I'm gonna post this here in case someone runs into it:

The problem is with MySQL, as stated in this post, mysql allows multiple null values in a unique constraint, so I had to change the default of deleted_id to 0 and now it works.

answered Jun 04 '20 at 15:43

Lucas Abbade

757
9
19

Or you could perhaps also allow for nulls as per this answer:https://stackoverflow.com/questions/33307892/django-unique-together-with-nullable-foreignkey – KenBuckley Mar 21 '22 at 14:31

Django unique constraint not working properly

1 Answers1