Django DB constraint - allow only one object with status field that is not "draft"

Question

I have a model Application with fields bank, mortgage and status. For same bank and mortgage I want to allow only one application with status different from DRAFT.

class Application(..):
    bank = ..
    mortgage = ..
    status = .. # draft, active, in_progress etc.

So there can be multiple applications like these:

# ok
Application(bank=1, mortgage=2, status='draft') 

# ok, tuple mortgage and bank already exists but but status is draft
Application(bank=1, mortgage=2, status='draft') 

# ok, tuple mortgage and bank already exists but status is active
Application(bank=1, mortgage=2, status='active') 

# ERROR - tuple mortgage and bank already exists and there is already one object with non-draft status
Application(bank=1, mortgage=2, status='in_progress') 

# ok - bank is different
Application(bank=3, mortgage=2, status='active') 

In words, I can't create an application with same bank and same mortgage and status that is not draft if there is already an application with same bank and mortgage and status that is not draft.

Is it possible to do such constraint? Would CheckConstraint work?

Answer 1

You can use a UniqueConstraint with a condition

class Application(models.Model):
    ...

    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=['bank', 'mortgage'],
                condition=~models.Q(status='draft'),
                name='unique_non_draft'
            )
        ]

The above creates a unique constraint on combinations of bank and mortgage but only when the status is not "draft"