Using Django's CheckConstraint with annotations

Question

I have a Django model where each instance requires a unique identifier that is derived from three fields:

class Example(Model):
    type = CharField(blank=False, null=False)           # either 'A' or 'B'
    timestamp = DateTimeField(default=timezone.now)
    number = models.IntegerField(null=True)             # a sequential number

This produces a label of the form [type][timestamp YEAR][number], which must be unique unless number is null.

I thought I might be able to use a couple of annotations:

uid_expr = Case(
    When(
        number=None,
        then=Value(None),
    ),
    default=Concat(
        'type', ExtractYear('timestamp'), 'number',
        output_field=models.CharField()
    ),
    output_field=models.CharField()
)

uid_count_expr = Count('uid', distinct=True)

I overrode the model's manager's get_queryset to apply the annotations by default and then tried to use CheckConstraint:

class Example(Model):
    ...

    class Meta:
        constraints = [
            models.CheckConstraint(check=Q(uid_cnt=1), name='unique_uid')
        ]

This fails because it's unable to find a field on the instance called uid_cnt, however I thought annotations were accessible to Q objects. It looks like CheckConstraint queries against the model directly rather than using the queryset returned by the manager:

class CheckConstraint(BaseConstraint):
    ...

    def _get_check_sql(self, model, schema_editor):
        query = Query(model=model)
    ...

Is there a way to apply a constraint to an annotation? Or is there a better approach?

I'd really like to enforce this at the db layer.

Thanks.

Answer 1

This is pseudo-code, but try:

class Example(Model):
    ...

    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=['type', 'timestamp__year', 'number'], 
                condition=Q(number__isnull=False),
                name='unique_uid'
            )
        ]