SQL 在 Oracle 的 Check 语句中使用子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4071024/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using subquery in a Check statement in Oracle
提问by devoured elysium
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?
所以我试图解决这个问题，但似乎最后一行（检查）不允许在其中进行子查询。有什么办法可以使这个工作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))
);
Here is the error info:
这是错误信息：
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.
回答by Justin Cave
There are three basic ways to solve this sort of problem since CHECK constraints cannot be based on a query.
由于 CHECK 约束不能基于查询，因此有三种基本方法可以解决此类问题。
Option 1: Triggers
选项 1：触发器
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.
最简单的方法是在 TANK 上放置一个触发器，它会查询 TANKS 并在 LEVEL 超过 CAPACITY 时抛出异常。然而，这种简单化方法的问题在于，几乎不可能正确处理并发问题。如果会话 1 减少 CAPACITY，然后会话 2 增加 LEVEL，然后两个事务提交，触发器将无法检测到违规。如果很少修改其中一个或两个表，这可能不是问题，但通常这将是一个问题。
Option 2: Materialized views
选项 2：物化视图
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.
您可以通过创建一个连接 TANK 和 TANKS 表的 ON COMMIT 物化视图，然后在物化视图上创建一个 CHECK 约束来验证 LEVEL <= CAPACITY 来解决并发问题。您还可以通过让物化视图仅包含违反约束的数据来避免存储数据两次。这将需要两个基表上的物化视图日志，这将为插入增加一些开销（尽管少于使用触发器）。将检查推送到提交时间将解决并发问题，但它引入了一些异常管理问题，因为 COMMIT 操作现在可能会因为物化视图刷新失败而失败。您的应用程序需要能够处理该问题并提醒用户注意这一事实。
Option 3: Change the data model
选项 3：更改数据模型
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.
如果表 A 中的值取决于表 B 中的限制，则可能表明 B 中的限制应该是表 A 的属性（而不是表 B 的属性，或者除了是表 B 的属性之外）。当然，这取决于您的数据模型的具体情况，但通常值得考虑。
回答by Tony Andrews
No unfortunately CHECK constraints cannot contain subqueries - see documentation.
不幸的是 CHECK 约束不能包含子查询 - 请参阅文档。
回答by Lukas Eder
The feature you're looking for is called SQL assertions, and it's not yet implemented in Oracle 12c
您正在寻找的功能称为 SQL 断言，它尚未在 Oracle 12c 中实现
回答by Jeffrey Kemp
Justin's answer has some good ideas. Another one is to wrap all inserts/updates to the table with a package (a TAPI, if you will), and implement the checks there. You'll need to ensure that all applications use your TAPI. You'll also need to implement some custom locking to protect the constraint from the effects of concurrent activity.
贾斯汀的回答有一些好主意。另一种方法是用一个包（TAPI，如果你愿意的话）将所有插入/更新包装到表中，并在那里实施检查。您需要确保所有应用程序都使用您的 TAPI。您还需要实现一些自定义锁定来保护约束免受并发活动的影响。
回答by Markus Winand
You will probably need to create triggers and use RAISE_APPLICATION_ERRORif it's outside the allowed range.
RAISE_APPLICATION_ERROR如果超出允许范围，您可能需要创建触发器并使用。
