Unique together involving multiple foreign keys & a many to many field

Since only few d entities had to have a corresponding price (rest will continue to have a generic common price), I ended up with the following structure.

class PricingTable(models.Model):
    a = models.ForeignKey(A, on_delete=models.CASCADE)
    price = MoneyField()
    b = ArrayField(models.CharField(choices=CHOICES))
    c = models.ForeignKey(C, on_delete=models.CASCADE)
    d = models.ForeignKey("x.D", on_delete=models.CASCADE, blank=True, null=True)

    class Meta:
        ordering = ("a",)
        unique_together = ("a", "b", "c", "d")

    def validate_b(self):
        # b can't be empty
        if not len(self.b) >= 1:
            raise ValueError
        # each element in b needs to be unique
        if not len(self.b) == len(set(self.b)):
            raise ValueError
        # each element in b needs to be unique together with a, c & d
        query = PricingTable.objects.filter(
            a=self.a, c=self.c, d=self.d, b__overlap=self.b
        ).exclude(pk=self.pk)
        if query.count() > 0:
            raise ValueError

    def save(self, *args, **kwargs):
        self.validate_b()
        return super().save(*args, **kwargs)


class DBasedPricing(models.Model):
    """
    Lookup table that tells (row exists) if we have D based pricing coupled with param A
    If we do, query PricingTable.d=d, else PricingTable.d=None for correct pricing
    """
    d = models.ForeignKey("x.D", on_delete=models.CASCADE)
    a = models.ForeignKey(A, on_delete=models.CASCADE)

    class Meta:
        unique_together = ("d", "a")

This forces me to first do a lookup based on d parameter, to check if pricing would be D based or not

d_id = None
if DBasedPricing.objects.filter(d_id=input_param.d, a_id=a.id).exists():
    d_id = input_param.d

Which then adds another parameter to my usual queries

price_obj = PricingTable.objects.filter(...usual query..., d_id=d_id)

Overall, at the cost of a single simple indexed lookup, I save on rows & of course complex DB structuring. Also, I ended up not having to re-enter all the existing pricing!

Premises

In Sql and so in Django ORM you can't set a unique constraints on a many to many fields because it involves two different tables.

SQL Solution:

You can try to reproduce this solution on django.

But in order to do this you have to manually create the tab_constr and insert the trigger logic inside the save method or with the signals

Django solution

I do not recommend you to follow that solution because it is difficult to reproduce in django, in fact you have to manually reproduce the m2m reference with two external key and one extra table.

Simply put your check on on_save method there is no other way.

P.S.

Don't use the override of save method to add check on your object because this method is not called if you change a QuerySet of objects. Instead use the signal like this:

@receiver(post_save, sender=Program)
def on_save_pricing_table(sender, instance, created, **kwargs):
    if not instance.value = 10:
        raise ValueError

You should try overlap to replace

# each element in b needs to be unique together with a & c
for i in self.b:
    query = PricingTable.objects.filter(
        a=self.a, c=self.c, b__contains=[i]
    ).exclude(pk=self.pk)
    if query.count() > 0:
        raise ValueError

by

query = PricingTable.objects.filter(
    a=self.a, c=self.c, b__overlap=self.b
).exclude(pk=self.pk)
if query.count() > 0:
            raise ValueError

Note: I did not verify the query generated and the performances