Implementing table level check constraint

Question

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

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.

Answer 1

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.

Answer 2

I've seen a concept of table-level (or set-level) constraint enforcement approach utilizing fast-refreshed materialized views.

The idea is to transform set-level requirements into a row-level requirements within a MV query, and then apply a conventional row-based check constraint to a materialized view row.

For example, if you want to limit a number of entries by a user to a certain amount, you create a select-count-group-by-user mat. view, and then apply check(mv_count_column <= desired_max) constraint.

However, due to a numerous restrictions for fast-refreshed matviews this approach would definitely be tricky to implement and to support. I'm not sure if it would work at all in your case, as analytic functions are not supported by fast-refreshed MVs - maybe you could be able to work it around.