I have two models:
flag = models.BooleanField()
owner = models.ForeignKey("Owner", on_delete=models.CASCADE)
n = models.IntegerField()
I want to constrain
n so that it must be positive if the owner’s
True, and negative otherwise.
However, if I add the following constraint to
constraints = [
check=Q(owner__flag=True, n__gt=0) | Q(owner__flag=False, n__lt=0),
then running the created migration fails with:
django.core.exceptions.FieldError: Joined field references are not permitted in this query
Is this a limitation of Django, or a database limitation? Is there another way to achieve this?
This is actually a PostgreSQL limitation. From their docs, the first Note in section 5.4.1:
PostgreSQL does not support
CHECK constraints that reference table data other than the new or updated row being checked.
(This is, of course, assuming you’re using PostgreSQL. If you’re using a different database, you might want to check the corresponding docs.)
The only near-100% certain solution I’m aware of would be to write a custom trigger to perform this validation within the database. (You can’t do this in Django, because bulk updates don’t send signals or use the model’s save method, which would bypass the two most likely places where you could put this code.)
This is a limitation in all databases, afaik. The only cross-table constraints allowed are foreign key constraints.
There’s probably a way involving a two field foreign key constraint and an extra table but it probably isn’t worth it.
If you really want such a constraint, you can instead use before-insert and before-update triggers to guard against the bad data cases. You’d need to create those with
RunSQL operations only though, since Django’s ORM doesn’t support them.
Hi Ken, hi Adam,
Thanks for your input. I’ve never had a good reason to learn about triggers… maybe today’s the day!