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'),
]
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
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 beeing 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.