CODEWITHSUNDEEP
pythondjangodjango-models
Andrey Volkov
Andrey Volkov

Reputation: 323

Django model constraint for related objects

I have the following code for models:

class Tag(models.Model):
    user = models.ForeignKey('auth.User', on_delete=models.CASCADE)

class Activity(models.Model):
    user = models.ForeignKey('auth.User', on_delete=models.CASCADE)
    tags = models.ManyToManyField(Tag, through='TagBinding')

class TagBinding(models.Model):
    tag = models.ForeignKey(Tag)
    activity = models.ForeignKey(Activity)

I want to write a database constraint on the TagBinding model using a new Django 2.2 syntax. This constraint should check that tag and activity fields of the TagBinding model have the same user. What I've tried to do:

class TagBinding(models.Model):
    tag = models.ForeignKey(Tag)
    activity = models.ForeignKey(Activity)

    class Meta:
        constraints = [
            models.CheckConstraint(
                name='user_equality',
                check=Q(tag__user=F('activity__user')),
            )
        ]

But this doesn't work because Django doesn't allow to use joins inside of the F function. Also Subquery with OuterRef didn't work for me because models that were referenced in a query were not registered.

Is there any way I can implement this constraint using a new syntax without raw SQL?

Update

It seems like some SQL backends don't support joins in constraints definition, so the question now: is it even possible to implement this behavior in the relational database?

Upvotes: 22

Views: 4600

Answers (1)

Matthew Schinckel
Matthew Schinckel

Reputation: 35639

In Postgres, there are two types of constraints (other than things like unique and foreign key constraints), CHECK CONSTRAINTS and EXCLUDE constraints.

Check constraints can only apply to a single row.

Exclusion constraints can only apply to a single table.

You will not be able to use either of these to enforce the constraint you want, which crosses table boundaries to ensure consistency.

What you could use instead are trigger-based constraints, that can perform other queries in order to validate the data.

For instance, you could have a BEFORE INSERT OR UPDATE trigger on the various tables that checks the users match. I have some similar code that runs on same self-relation tree code that ensures a parent and child both have the same "category" as one another.

In this case, it's going to be a bit trickier, because you would need some mechanism of preventing the check until all tables involved have been updated.

Upvotes: 8

Related Questions