Error Using CheckConstraint in Model.Meta along with Django GenericForeignKey - Joined field references are not permitted in this query

Question

I am trying to restrict GFK to be pointed to objects of a few models only, and I thought CheckConstraint will be a great way to do this, however I get this error

class ManualAdjustment(Model):
    content_type = models.ForeignKey(ContentType, null=True, on_delete=models.CASCADE)
    object_id = models.PositiveIntegerField(null=True)

    booking_obj = GenericForeignKey('content_type', 'object_id')  
    # should point to a app1.Booking1 or app2.Booking2 or app3.Booking3 only - trying to enforce this via CheckConstraint


    class Meta:
        constraints = [
            models.CheckConstraint(
                check=
                Q(content_type__app_label='app1', content_type__model='booking1') |
                Q(content_type__app_label='app2', content_type__model='booking2') |
                Q(content_type__app_label='app3', content_type__model='booking3'),
                name='myconstraint_only_certain_models'),
        ]

Error I get on migrate

    execute_from_command_line(sys.argv)
  File "/Users/myuser/.virtualenvs/xenia371/lib/python3.7/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "/Users/myuser/.virtualenvs/xenia371/lib/python3.7/site-packages/django/core/management/__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/myuser/.virtualenvs/xenia371/lib/python3.7/site-packages/django/core/management/base.py", line 323, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/Users/myuser/.virtualenvs/xenia371/lib/python3.7/site-packages/django/core/management/commands/sqlmigrate.py", line 30, in execute
    return super().execute(*args, **options)
  File "/Users/myuser/.virtualenvs/xenia371/lib/python3.7/site-packages/django/core/management/base.py", line 364, in execute
    output = self.handle(*args, **options)
  File "/Users/myuser/.virtualenvs/xenia371/lib/python3.7/site-packages/django/core/management/commands/sqlmigrate.py", line 64, in handle
    sql_statements = executor.collect_sql(plan)
  File "/Users/myuser/.virtualenvs/xenia371/lib/python3.7/site-packages/django/db/migrations/executor.py", line 225, in collect_sql
    state = migration.apply(state, schema_editor, collect_sql=True)
  File "/Users/myuser/.virtualenvs/xenia371/lib/python3.7/site-packages/django/db/migrations/migration.py", line 124, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/Users/myuser/.virtualenvs/xenia371/lib/python3.7/site-packages/django/db/migrations/operations/models.py", line 827, in database_forwards
    schema_editor.add_constraint(model, self.constraint)
  File "/Users/myuser/.virtualenvs/xenia371/lib/python3.7/site-packages/django/db/backends/base/schema.py", line 343, in add_constraint
    sql = constraint.create_sql(model, self)
  File "/Users/myuser/.virtualenvs/xenia371/lib/python3.7/site-packages/django/db/models/constraints.py", line 47, in create_sql
    check = self._get_check_sql(model, schema_editor)
  File "/Users/myuser/.virtualenvs/xenia371/lib/python3.7/site-packages/django/db/models/constraints.py", line 37, in _get_check_sql
    where = query.build_where(self.check)
  File "/Users/myuser/.virtualenvs/xenia371/lib/python3.7/site-packages/django/db/models/sql/query.py", line 1296, in build_where
    return self._add_q(q_object, used_aliases=set(), allow_joins=False, simple_col=True)[0]
  File "/Users/myuser/.virtualenvs/xenia371/lib/python3.7/site-packages/django/db/models/sql/query.py", line 1312, in _add_q
    current_negated, allow_joins, split_subq, simple_col)
  File "/Users/myuser/.virtualenvs/xenia371/lib/python3.7/site-packages/django/db/models/sql/query.py", line 1318, in _add_q
    split_subq=split_subq, simple_col=simple_col,
  File "/Users/myuser/.virtualenvs/xenia371/lib/python3.7/site-packages/django/db/models/sql/query.py", line 1199, in build_filter
    raise FieldError("Joined field references are not permitted in this query")
django.core.exceptions.FieldError: Joined field references are not permitted in this query

Any clue on how to solve this? I have used GFK before but with new checkconstraint it actually now can be a nicely error-safe way, if I could get this to migrate

Thanks

Jaquenette

Answer 1

It is not possible to achieve this using the CheckConstraint functionality. Django translates all ORM commands to the low level DB specific commands, and such constraint creation isn't possible even on the DB level. In fact, we can apply CheckConstraint to a single row being added/updated only.

The note in documentation on PostgreSQL says:

PostgreSQL does not support CHECK constraints that reference table data other than the new or updated row being checked. While a CHECK constraint that violates this rule may appear to work in simple tests, it cannot guarantee that the database will not reach a state in which the constraint condition is false (due to subsequent changes of the other row(s) involved). This would cause a database dump and reload to fail. The reload could fail even when the complete database state is consistent with the constraint, due to rows not being loaded in an order that will satisfy the constraint. If possible, use UNIQUE, EXCLUDE, or FOREIGN KEY constraints to express cross-row and cross-table restrictions.

If what you desire is a one-time check against other rows at row insertion, rather than a continuously-maintained consistency guarantee, a custom trigger can be used to implement that. (This approach avoids the dump/reload problem because pg_dump does not reinstall triggers until after reloading data, so that the check will not be enforced during a dump/reload.)

So, the only way to introduce desired constrains is using db triggers. You can create empty migration and add DB trigger into it.

Idiopathy