django - 在Model.Meta中与Django GenericForeignKey一起使用CheckConstraint时出错-此查询中不允许联接的字段引用

Question

我试图将GFK限制为仅指向少数模型的对象，并且我认为CheckConstraint将是实现此目的的一种好方法，但是出现此错误

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

关于如何解决这个问题的任何线索？我以前曾使用过GFK，但现在有了新的checkconstraint，如果可以将其迁移，它实际上可以成为一种很好的错误安全方法

谢谢

Answer 1

使用CheckConstraint功能无法实现此目的。 Django将所有ORM命令转换为特定于底层数据库的命令，即使在数据库级别，也无法创建此类约束。实际上，我们只能将CheckConstraint应用于仅添加/更新的单个行。PostgreSQL上documentation中的注释说:

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.)

因此，引入所需约束的唯一方法是使用db触发器。您可以创建空迁移并将数据库触发器添加到其中。