CODEWITHSUNDEEP
pythondjango
Sadiq
Sadiq

Reputation: 31

Django DM model constraint: uniqueConstraint or checkConstraint?

I am trying to define a unique constraint for my DB model. I have 3 constraints and have no trouble with 2. But the 3rd one is doing my head a little.

So the model class is fairly simply; its a social media connection request model (between user accounts). These are the constraints I am trying to enforce:

  1. User1 should have only one connection request to User2: UniqueConstraint works fine.
  2. User1 should not be able to send a connection request to User1 (ie self): CheckConstraint works fine.
  3. If User1 sends a connection request to User2, then User2 should not be able to send a connection request back to User1: this is the one I am struggling with.

How can I enforce the 3rd constraint here? I initially thought the 1st one enforces the reverse constraint actually (where the uniqueness of the 2 entries in the table are enforced regardless of the order) but it doesnt seem it like (and makes sense too).

class ConnectionRequest(models.Model):

    senderAccount = models.ForeignKey(
        Account, related_name='sender_Account', on_delete=models.CASCADE)
    recipientAccount = models.ForeignKey(
        Account, related_name='recipient_Account', on_delete=models.CASCADE)
    requested = models.DateTimeField(auto_now_add=True)

    def __str__(self):
        return str(self.senderAccount.id) + ' to ' + str(self.recipientAccount.id)

    class Meta:
        constraints = [
            # duplicate connection request constraint
            models.UniqueConstraint(name='duplicate_connreq_constraint',
                                    fields=['senderAccount', 'recipientAccount']),

            # self connection request constraint
            models.CheckConstraint(name='self_connection_request_constraint',
                                   check=~models.Q(
                                       senderAccount=models.F("recipientAccount")),
                                   ),
        ]

Upvotes: 1

Views: 1183

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477230

Since , you could make a functional unique constraint with the Least [Django-doc] and Greatest [Django-doc] of the two, so:

from django.db.models.functions import Least, Greatest


class ConnectionRequest(models.Model):
    senderAccount = models.ForeignKey(
        Account, related_name='sender_Account', on_delete=models.CASCADE
    )
    recipientAccount = models.ForeignKey(
        Account, related_name='recipient_Account', on_delete=models.CASCADE
    )
    requested = models.DateTimeField(auto_now_add=True)

    def __str__(self):
        return f'{self.senderAccount_id}  to {self.recipientAccount_id}'

    class Meta:
        constraints = [
            models.UniqueConstraint(
                name='duplicate_connreq_constraint',
                fields=['senderAccount', 'recipientAccount'],
            ),
            models.CheckConstraint(
                name='self_connection_request_constraint',
                check=~models.Q(senderAccount=models.F("recipientAccount")),
            ),
            models.UniqueConstraint(
                Least('senderAccount', 'recipientAccount'),
                Greatest('senderAccount', 'recipientAccount'),
                name='antisymmetric',
            ),
        ]

Upvotes: 1

Related Questions