Django CheckConstraint failing

Question

I'm creating a custom user-model where every user needs a company assigned, unless they are a superuser or staff.

To accomplish this, I'm adding 3 CheckConstrains as seen below:

class CustomUser(AbstractUser):
    '''
    Custom User class, username and email are required. 
    Company is required except for staff and super users
    '''
    company = models.ForeignKey(Company, on_delete=models.PROTECT, null=True, blank=True)

    class Meta(AbstractUser.Meta):
        constraints = [
            CheckConstraint(
                check=Q(is_superuser=False, is_staff=False, company__isnull=False),
                name="user_must_have_company",
            ),
            CheckConstraint(
                check=Q(is_staff=True, company__isnull=True),
                name="staff_cannot_have_company",
            ),
            CheckConstraint(
                check=Q(is_superuser=True, company__isnull=True),
                name="superuser_cannot_have_company",
            ),
        ]

Desired behaviour is:

• Super-users and Staff users cannot have a company
• all 'normal' users need to have a company set.

However, all initial conditions fail on the same CheckConstraint user_must_have_company.

user = CustomUser.objects.create(username='staffuser', is_staff=True, company=None)
IntegrityError: CHECK constraint failed: user_must_have_company

company = Company.objects.create(name='Test')
user = CustomUser.objects.create(username='normal_user', is_staff=True, company=company)
IntegrityError: CHECK constraint failed: user_must_have_company

user = CustomUser.objects.create(username='superuser', is_superuser=True, company=None)
IntegrityError: CHECK constraint failed: user_must_have_company

What am I missing?

Answer 1

Your checks here say that a user needs to have is_superuser=False, and is_staff=False, and company should not be None/NULL, so this is not a conditional statement, it implies that the is_superuser should always be False. The next check contradicts this by saying that is_staff should be True and company should be None/NULL, and finally the last one says that is_superuser should be True and company should be None/NULL.

What you can do is to work with the disjunctive form of an implication. Indeed, in boolean logic, the expression A → B is equivalent to ¬A ∨ B, so the condition (A) does not hold, or if it holds, its implication (B) should hold. This means that the checks are equivalent to:

class CustomUser(AbstractUser):
    # …

    class Meta(AbstractUser.Meta):
        constraints = [
            CheckConstraint(
                check=Q(is_superuser=True)
                | Q(is_staff=True)
                | Q(company__isnull=False),
                name='user_must_have_company',
            ),
            CheckConstraint(
                check=Q(is_staff=False) | Q(company=None),
                name="staff_cannot_have_company",
            ),
            CheckConstraint(
                check=Q(is_superuser=False) | Q(company=None),
                name="superuser_cannot_have_company",
            ),
        ]

In this particular case however, we can simplify this to:

class CustomUser(AbstractUser):
    # …

    class Meta(AbstractUser.Meta):
        constraints = [
            CheckConstraint(
                check=Q(is_superuser=False, is_staff=False) ^ Q(company=None),
                name='user_has_company_iff_no_staff_admin',
            ),
        ]