SQLAlchemy中的复杂外键约束

Question

我有两个表，一个叫 SystemVariables，另一个叫 VariableOptions。SystemVariables 的意思应该很明显，而 VariableOptions 则包含了所有变量可能的选择。

VariableOptions 有一个外键 variable_id，它表示这个选项是哪个变量的选择。SystemVariables 也有一个外键 choice_id，它表示当前选中的选项。

我通过在 choice_id 上使用 use_alter 和在 SystemVariables 的 choice 关系上使用 post_update 来解决这个循环关系。不过，我想添加一个额外的数据库约束，以确保 choice_id 是有效的（也就是说，它指向的选项是可以回指到它的）。

我需要的逻辑是，假设 sysVar 代表 SystemVariables 表中的一行，基本上是这样的：

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

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

这可能吗？

Answer 1

我真的不喜欢循环引用。通常有办法避免它们。这里有一种方法：

SystemVariables 
---------------
  variable_id 
  PRIMARY KEY (variable_id)


VariableOptions 
---------------
  option_id 
  variable_id 
  PRIMARY KEY (option_id)
  UNIQUE KEY (variable_id, option_id) 
  FOREIGN KEY (variable_id) 
    REFERENCES SystemVariables(variable_id)


CurrentOptions
--------------
  variable_id 
  option_id 
  PRIMARY KEY (variable_id)
  FOREIGN KEY (variable_id, option_id)
    REFERENCES VariableOptions(variable_id, option_id)

Answer 2

编辑： SQLAlchemy的0.7.4版本（我在2011年7月12日开始询问这个问题的同一天发布！）引入了一个新的autoincrement值，专门用于那些既是主键又是外键的情况，叫做ignore_fk。文档也更新了，里面有个很好的例子，正好说明了我最初想要实现的内容。

现在所有内容都在这里解释得很清楚。

如果你想看看我在这个版本发布之前写的代码，可以查看这个回答的修订历史。

Answer 3

你可以做到这一点，不需要什么花招。只需要扩展外键，让它除了包含choice_id之外，还包括variable_id。

这里有一个可以运行的示例。使用临时表，你可以轻松地进行测试：

CREATE TABLE systemvariables (
  variable_id int PRIMARY KEY
, choice_id   int
, variable    text
);
   
INSERT INTO systemvariables(variable_id, variable) VALUES
  (1, 'var1')
, (2, 'var2')
, (3, 'var3')
;

CREATE TABLE variableoptions (
  option_id   int PRIMARY KEY
, variable_id int REFERENCES systemvariables ON UPDATE CASCADE ON DELETE CASCADE
, option      text
, UNIQUE (option_id, variable_id)  -- needed for the FK
);

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

这正是你想要的效果。

所有关键列都设置为 NOT NULL

我觉得在后面的回答中找到了一个更好的解决方案：

• 如何处理相互依赖的插入

针对@ypercube在评论中的问题，为了避免出现未知关联的条目，所有关键列，包括外键，都要设置为NOT NULL。

通常情况下，循环依赖会让这变得不可能。这就像经典的鸡和蛋问题：其中一个必须先存在，才能生成另一个。但是大自然找到了办法，Postgres也一样：可延迟的外键约束。

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

CREATE TABLE variableoptions (
  option_id   int PRIMARY KEY
, option      text
, 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; -- 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约束不能被延迟，它会立即生效。但是外键约束可以被延迟，因为我们是这样定义的。它会在事务结束时检查，这样就避免了鸡和蛋的问题。

在这个编辑过的场景中，两个外键都是可延迟的。你可以以任意顺序输入变量和选项。
如果你在一个语句中使用CTE同时输入两个表中的相关条目，甚至可以让它在普通的非延迟外键约束下工作，具体细节可以参考链接的回答。

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

另一方面，第二个外键有CASCADE修饰符，并且仍然被定义为DEFERRABLE。这有一些限制。手册中提到：

除了NO ACTION检查之外，其他引用操作不能被延迟，即使约束被声明为可延迟。

NO ACTION是默认设置。

因此，INSERT时的引用完整性检查是可以延迟的，但声明的级联操作在DELETE和UPDATE时则不可以。在PostgreSQL 9.0或更高版本中，以下操作是不被允许的，因为约束在每个语句后都会被强制执行：

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

详细信息：

• 定义为DEFERRABLE INITIALLY IMMEDIATE的约束仍然是延迟的吗？