实现表级检查约束

本文介绍了实现表级检查约束的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我们有一个表格，其中包含取决于基本金额的价格。例如，假设如果基本金额小于或等于100，那么价格是10，但是如果基本金额大于100但小于或等于1000，那么价格是20，最后如果基本金额大于1000，那么价格是30.我们的表格的简化版本应该是这样：

We have a table that contains prices that are depending on a base amount. As an example let say that if the base amount is less or equal to 100 then the price is 10 but if the base amount is greater that 100 but less or equal to 1000 then the price is 20 and finally if the base amount is greater than 1000 then the price is 30. A simplified version of our table for this should be something like this:

PRICE_CODE START_RANGE END_RANGE PRICE_AMOUNT 100 0,00 100,00 10,00 100 100,01 1000,00 20,00 100 1000,01 99999999,99 30,00 110 0,00 99999999,99 15,00

使用列级别检查约束，您可以轻松确保每个记录包含有效范围信息。问题是，我们还需要某种表级检查约束，以确保每个价格代码的范围信息不包含任何重叠或间隙，如下例所示：

With columns level check constraints you can easily make sure that each record is containing valid range information. The problem is that we should also need some kind of table level check constraint to make sure that the range information for each price code doesn't contain any overlap or gaps like in this example:

PRICE_CODE START_RANGE END_RANGE PRICE_AMOUNT 100 0,00 200,00 10,00 100 100,01 1000,00 20,00 100 1100,01 99999999,99 30,00

过程，但工作，但问题是我没有发现任何地方在数据库中调用验证逻辑。自然地，您不能放置在记录级触发器中，但是当可以执行单独的插入，更新和删除时，语句级别也不会触发工作，并且只应对最终结果验证范围。验证逻辑应该是这样：

I have created a validation procedure that is working but the problem is that I haven't found any place in the database to call the validation logic from. Naturally you can't place in a record level trigger but neither will the statement level trigger work when it is possible to do separate inserts, updates and deletes and the ranges should only be validated for the end result. The validation logic should be something like this:

SELECT * FROM ( SELECT price_code, start_range, end_range, price_amount , lag (end_range) OVER (PARTITION BY price_code ORDER BY end_range) prev_end , lead (start_range) OVER (PARTITION BY price_code ORDER BY start_range) next_start FROM my_test ORDER BY price_code, start_range, end_range) WHERE start_range <= prev_end OR end_range >= next_start OR (next_start - end_range) > 0.01 OR (start_range - prev_end) > 0.01

一种方法当然是将验证逻辑放入数据访问层，可能通过直接使用SQL来规避验证。我感兴趣的是，如果有人有一些想法如何在数据库中实现这种表级约束，以确保没有人将能够提交无效的范围数据。我们使用Oracle所以主要我对基于Oracle的解决方案感兴趣，但我也感兴趣任何其他RDBMS如何解决这个问题。

One way is of course to put the validation logic in the data access layer but then it is still possible to circumvent the validation by directly using SQL. What I'm interested in is if anyone have some ideas how to implement this kind of "table level constraint" in the database to make sure that no one will ever be able to commit invalid range data. We are using Oracle so primary I'm interested in Oracle based solution but I'm also interested how any other RDBMS have solved this problem.

推荐答案

end_range列是否必需？ end_range值也可以是下一个更高的start_range值。如果您这样做，则间隙和重叠是不可能的。

Is the end_range column necessary? The end_range value could also be the next higher start_range value. Gaps and overlaps are not possible if you do it this way.