在Oracle中的Check语句中使用子查询 [英] Using subquery in a Check statement in Oracle

问题描述

所以我试图工作，但似乎最后一行（检查）不允许子查询。

So I was trying to work this out but it seems that the last line (the check) doesn't allow sub queries in it. Any way to make this work 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.

推荐答案

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

There are three basic ways to solve this sort of problem since CHECK constraints cannot be based on a query.

选项1：触发

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

The most simplistic approach would be to put a trigger on TANK that queries TANKS and throws an exception if the LEVEL exceeds CAPACITY. The problem with this sort of simplistic approach, though, is that it is nearly impossible to handle concurrency issues correctly. If session 1 decreases the CAPACITY, then session 2 increases the LEVEL, and then both transactions commit, triggers will not be able to detect the violation. This may not be an issue if one or both of the tables are seldom modified, but in general it's going to be an issue.

选项2：物化视图

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

You can solve the concurrency issue by creating an ON COMMIT materialized view that joins the TANK and TANKS table and then creating a CHECK constraint on the materialized view that verifies that the LEVEL <= CAPACITY. You can also avoid storing the data twice by having the materialized view contain just data that would violate the constraint. This will require materialized view logs on both the base tables which will add a bit of overhead to inserts (though less than using triggers). Pushing the check to commit-time will solve the concurrency issue but it introduces a bit of an exception management issue since the COMMIT operation can now fail because the materialized view refresh failed. Your application would need to be able to handle that problem and to alert the user to that fact.

选项3：更改数据模型

如果表A中的值取决于表B中的限制，那么表示B中的限制应该是表A的属性（而不是或者除了是表B的属性之外）。这取决于你的数据模型的细节，当然，但它通常值得考虑。

If you have a value in table A that depends on a limit in table B, that may indicate that the limit in B ought to be an attribute of table A (instead of or in addition to being an attribute of table B). It depends on the specifics of your data model, of course, but it's often worth considering.

这篇关于在Oracle中的Check语句中使用子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！