Complex constraint in Django with Q Objects

Question

Two players can register for a tennis tournament which offers the doubles discipline (2 players compete against 2 other players).

But there are some constraints:

1. The same player may not register as player_01 for the same competition twice (same for player_02)
2. A player may not register as player_01 if already registered as player_02 (with different partner) for the same competition (and vice versa)

class Registration(models.Model):
    class Meta:
        abstract = True
    
    competition = models.ForeignKey(Competition, on_delete=models.CASCADE)


class DoubleRegistration(Registration):
    class Meta:
        constraints = [
            # 1. The same player may not register as player_01 for the same competition twice (same for player_02)
            models.UniqueConstraint(
                fields=['competition', 'player_01'], 
                name='double_registration_unique_pl01'),
            models.UniqueConstraint(
                fields=['competition', 'player_02'], 
                name='double_registration_unique_pl02'),
            # 2. A player may not register as player_01 if already registered as player_02 (with different partner) for the same competition (and vice versa)
            models.CheckConstraint(
                 check=~Q(player_01=F('player_02') , competition=F('competition')),
                 name='double_registration_pl01_already_registered'),
            models.CheckConstraint(
                check=~Q(player_02=F('player_01'), competition=F('competition')),
                name='double_registration_pl02_already_registered'),  
        ]

    player_01 = models.ForeignKey(Player, on_delete=models.CASCADE, related_name = 'doubles_player_01')
    player_02 = models.ForeignKey(Player, on_delete=models.CASCADE, related_name = 'doubles_player_02')

While the first constraint seems to work fine, the second constraint does not work.

I am still able to first register Adam as player_01 and Kain as player_02 (which is fine) but then also register Adam as player_02 and Kain as player_01. Since the second constraint checks if Adam has already been registered as player_01 for the same competition this should fail, but doesn't.

Answer 1

CheckConstraint constraints are not enforced at the database level. The check argument probably can't evaluate True or False given your code. Try custom validation method in the DoubleRegistration model. If that's not an option use a database-level trigger or maybe a database-level unique constraint. Your code might not prevent the same player from registering twice. Will this work instead?

class Registration(models.Model):
    class Meta:
        abstract = True
    
    competition = models.ForeignKey(Competition, on_delete=models.CASCADE)


class DoubleRegistration(Registration):
    class Meta:
        constraints = [
            # 1. The same player may not register as player_01 for the same competition twice (same for player_02)
            models.UniqueConstraint(
                fields=['competition', 'player_01'], 
                name='double_registration_unique_pl01'),
            models.UniqueConstraint(
                fields=['competition', 'player_02'], 
                name='double_registration_unique_pl02'),
        ]

    player_01 = models.ForeignKey(Player, on_delete=models.CASCADE, related_name = 'doubles_player_01')
    player_02 = models.ForeignKey(Player, on_delete=models.CASCADE, related_name = 'doubles_player_02')