在Oracle中的Check语句中使用子查询

Question

所以我试图解决这个问题，但似乎最后一行（检查）不允许在其中进行子查询。有什么办法可以使Oracle工作吗？

CREATE TABLE Tank (
    n_id            int,
    day             date,
    level           int,
    CONSTRAINT pk_w_td PRIMARY KEY (n_id,day),
    CONSTRAINT fk_w_td_tan FOREIGN KEY (n_id) REFERENCES Tanks ON DELETE CASCADE,
    CHECK (level > 0 AND level <= (SELECT capacity FROM Tanks WHERE Tanks.n_id = TanksDay.n_id))
);

这是错误信息：

Error at Command Line:7 Column:32 Error report: SQL Error: ORA-02251: subquery not allowed here
02251. 00000 -  "subquery not allowed here"
*Cause:    Subquery is not allowed here in the statement.
*Action:   Remove the subquery from the statement.

Answer 1

由于CHECK约束不能基于查询，因此有三种基本方法可以解决此类问题。

选项1：触发器

最简单的方法是在TANK上放置一个触发器，以查询TANKS并在LEVEL超过CAPACITY时引发异常。但是，这种简单化方法的问题在于，几乎不可能正确处理并发问题。如果会话1降低了容量，则会话2增加了LEVEL，然后两个事务都提交，触发器将无法检测到违规。如果很少修改一个或两个表，这可能不是问题，但总的来说，这将是一个问题。

选项2：实例化视图

您可以通过创建连接TANK和TANKS表的ON COMMIT实例化视图，然后在该实例化视图上创建一个CHECK约束来验证LEVEL <=
CAPACITY来解决并发问题。您还可以通过使实例化视图仅包含违反约束的数据来避免将数据存储两次。这将需要两个基表上的物化视图日志，这将增加插入的开销（尽管比使用触发器要少）。将检查推送到提交时间将解决并发问题，但由于COMMIT操作现在可能由于物化视图刷新失败而失败，因此引入了一些异常管理问题。您的应用程序需要能够处理该问题并向用户发出警告。

选项3：更改数据模型

如果表A中的值取决于表B中的限制，则可能表明表B中的限制应为表A的属性（而不是表B的属性或除了表B的属性外）。当然，这取决于数据模型的细节，但是通常值得考虑。