如何在 SQLAlchemy 中实现复杂的外键约束以保证数据完整性？-mysql教程-PHP中文网

如何在 SQLAlchemy 中实现复杂的外键约束以保证数据完整性？

DDD

发布： 2024-12-20 06:26:09

原创

306 人浏览过

How to Implement Complex Foreign Key Constraints in SQLAlchemy for Data Integrity?

SQLAlchemy 中的复杂外键约束

概述

在两个表之间创建外键关系有时会带来挑战，尤其是在处理循环引用时并确保数据完整性。本文解决了涉及复杂外键约束的特定场景，该约束确保两个表中都存在引用数据。

问题陈述

考虑两个表：SystemVariables 和 VariableOptions。 SystemVariables 表示系统变量，而 VariableOptions 列出这些变量的可能选项。每个变量选项都有一个外键指向 SystemVariables 中相应的变量。反过来，每个系统变量都有一个外键引用 VariableOptions 中当前选择的选项。

问题的产生是因为需要额外的数据库约束来强制 SystemVariables 中的每个 choice_id 引用 VariableOptions 中的有效选项。本质上，choice_id和variable_id之间必须有直接关系。

建议的解决方案

解决方案在于扩展引用所选选项的外键以包含choice_id和variable_id。这可确保两个表中都存在引用。

以下是如何在 PostgreSQL 9.1 中实现此功能的示例：

CREATE TABLE systemvariables (
  variable_id int PRIMARY KEY,
  choice_id   int,
  variable    text
);

CREATE TABLE variableoptions (
  option_id   int PRIMARY KEY,
  variable_id int REFERENCES systemvariables ON UPDATE CASCADE ON DELETE CASCADE,
  option      text
);

ALTER TABLE systemvariables
  ADD CONSTRAINT systemvariables_choice_id_fk
  FOREIGN KEY (choice_id, variable_id) REFERENCES variableoptions(option_id, variable_id);

登录后复制

此扩展外键约束有效地强制执行所需的数据完整性规则。插入或更新违反此规则的记录将导致错误。

延迟外键约束

要处理两个外键均可延迟的情况，需要稍微不同的方法。在 PostgreSQL 中，DEFERRABLE INITIALLY DEFERRED 子句支持可延迟外键约束。这允许在两个表中插入相关条目，而无需立即强制执行外键约束。在交易结束时检查约束，解决了先有鸡还是先有蛋的问题。

以下是如何实现这一点的示例：

CREATE TABLE systemvariables (
  variable_id int PRIMARY KEY NOT NULL,
  variable    text NOT NULL,
  choice_id   int NOT NULL
);

CREATE TABLE variableoptions (
  option_id   int PRIMARY KEY NOT NULL,
  option      text NOT NULL,
  variable_id int NOT NULL REFERENCES systemvariables ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  UNIQUE (option_id, variable_id) -- needed for the foreign key
);

ALTER TABLE systemvariables
ADD CONSTRAINT systemvariables_choice_id_fk FOREIGN KEY (choice_id, variable_id)
   REFERENCES variableoptions(option_id, variable_id) DEFERRABLE INITIALLY DEFERRED;

登录后复制

这种方法确保了外国即使以任意顺序插入相关条目，密钥也会被强制执行。