SQLAlchemy中的复杂外键约束

Question

我有两个表，SystemVariables和VariableOptions。SystemVariables应该是不言自明的，并且VariableOptions包含所有变量的所有可能选择。

VariableOptions有一个外键，variable_id指出了它是哪个变量的选项。SystemVariables有一个外键，choice_id指出了当前选择的选项。

我用得到周围的循环关系use_alter上choice_id，并post_update在SystemVariables“choice关系。但是，我想添加一个额外的数据库约束，以确保该约束choice_id是有效的（即，它所指的是一个引用它的选项）。

我需要的逻辑（假设它sysVar代表SystemVariables表中的一行）基本上是：

VariableOptions[sysVar.choice_id].variable_id == sysVar.id

但是我不知道如何使用SQL，声明式或任何其他方法来构造这种约束。如有必要，我可以在应用程序级别进行验证，但如果可能的话，我希望在数据库级别进行验证。我正在使用Postgres
9.1。

这可能吗？

Answer 1

您可以实现该目标 而无需肮脏的把戏 。只是 延长外键 引用所选择的选项包括variable_id除choice_id。

这是一个工作示例。临时表，因此您可以轻松使用它：

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

INSERT INTO systemvariables(variable_id, variable)
VALUES
  (1, 'var1')
, (2, 'var2')
, (3, 'var3');

CREATE TEMP TABLE variableoptions (
  option_id integer PRIMARY KEY
, option text
, variable_id integer REFERENCES systemvariables(variable_id)
                      ON UPDATE CASCADE ON DELETE CASCADE
, 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);

INSERT INTO variableoptions
VALUES
  (1, 'var1_op1', 1)
, (2, 'var1_op2', 1)
, (3, 'var1_op3', 1)
, (4, 'var2_op1', 2)
, (5, 'var2_op2', 2)
, (6, 'var3_op1', 3);

允许选择关联的选项：

UPDATE systemvariables SET choice_id = 2 WHERE variable_id = 1;
UPDATE systemvariables SET choice_id = 5 WHERE variable_id = 2;
UPDATE systemvariables SET choice_id = 6 WHERE variable_id = 3;

但是并没有脱节：

UPDATE systemvariables SET choice_id = 7 WHERE variable_id = 3;
UPDATE systemvariables SET choice_id = 4 WHERE variable_id = 1;

ERROR:  insert or update on table "systemvariables" violates foreign key

constraint “systemvariables_choice_id_fk”
DETAIL: Key (choice_id,variable_id)=(4,1) is not present in table
“variableoptions”.

Voilá 。正是您想要的。

所有键列NOT NULL

我想我在后面的答案中找到了更好的解决方案：

• 如何处理相互递归的插入

在注释中解决@ypercube的问题，以避免关联不明的条目创建所有键列NOT NULL，包括外键。

循环依赖关系通常使之不可能。这是经典的 鸡肉 问题：两者必须首先出现才能产生另一个。但是大自然找到了解决之道，Postgres也找到了办法：
可延缓的外键约束 。

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

CREATE TEMP TABLE variableoptions (
  option_id   integer PRIMARY KEY
, option      text
, variable_id integer NOT NULL
     REFERENCES systemvariables(variable_id)
     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; -- no CASCADING here!

新 变量和关联的选项必须插入同一事务中：

BEGIN;

INSERT INTO systemvariables (variable_id, variable, choice_id)
VALUES
  (1, 'var1', 2)
, (2, 'var2', 5)
, (3, 'var3', 6);

INSERT INTO variableoptions (option_id, option, variable_id)
VALUES
  (1, 'var1_op1', 1)
, (2, 'var1_op2', 1)
, (3, 'var1_op3', 1)
, (4, 'var2_op1', 2)
, (5, 'var2_op2', 2)
, (6, 'var3_op1', 3);

END;

该NOT NULL约束不能被推迟，则立即执行。但是外键约束 可以 ，因为我们是这样定义的。在交易结束时检查它，避免了鸡蛋问题。

在此已 编辑 方案中， 两个外键都被推迟 。您可以按任意顺序输入变量和选项。

您可能已经注意到，第一个外键约束没有CASCADE修饰符。（允许更改variableoptions.variable_id级联回去是没有意义的。

另一方面，第二个外键具有CASCADE修饰符，并且仍被定义为可延迟的。这带有一些限制。手册：

NO ACTION即使约束被声明为可延迟的，也不能延迟检查以外的引用动作。

NO ACTION 是默认值。

因此，参照完整性检查就INSERT被推迟，但声明的级联的行动DELETE并UPDATE没有。在PostgreSQL 9.0或9.1中
，以下内容是不允许的，因为在每个语句之后都会强制执行约束：

UPDATE option SET var_id = 4 WHERE var_id = 5;
DELETE FROM var WHERE var_id = 5;

奇怪的是，同样的事情在PostgreSQL 8.4中也有效 ，而文档中声明了同样的行为。看起来像是旧版本中的错误-
即使乍一看似乎是有益的，而不是有害的。对于新版本，必须已修复。