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.

In standard SQL, this would be `CREATE ASSERTION`, but I've not worked on any RDBMS that has actually implemented this. — Damien_The_Unbeliever, Nov 20 '12 at 07:32
That is really interesting that it seems like no RDBMS have implemented anything like this. This is not the first time I have been running into the need for multi record validations. — Peter Å, Nov 20 '12 at 08:04
I believe the reason that nobody actually implements it is that it could easily become a phenomenally expensive cost added to every transaction that touches the table (unless the database system can find a way to be able to evaluate it in an incremental fashion - which either implies severe limitations on the assertion or severe limitations on the table) — Damien_The_Unbeliever, Nov 20 '12 at 09:10

score 4 · Accepted Answer · answered Nov 20 '12 at 07:40

4

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.

answered Nov 20 '12 at 07:40

Rene

10,391
5
33
46

+1 - each boundary needs to be stored only once; the ranges can easily be derived at query time. – Jeffrey Kemp Nov 20 '12 at 07:44
You are right that is one way of solving this kind of problems but in my opinion that will make it much more difficult to query the data. – Peter Å Nov 20 '12 at 07:45
1

select start_value ,(select min(start_value) from table t2 where t2.start_value > t1.start_value) as end_value from table t1 – Rene Nov 20 '12 at 08:27
Certainly that is one way to go but that us yet not the full solution. How do you easily get the end_value for the last range? – Peter Å Nov 20 '12 at 08:30
Add another range with start value= or use NVL(,) to generate a last end value – Rene Nov 20 '12 at 09:07
Ok, I guess that will fix that problem. I have seen that kind of solution and in my humble opinion not that straightforward. Make the code somewhat harder to support but your right that it will fix the problem with the ranges. – Peter Å Nov 20 '12 at 09:18
I've used a similar method to replace specification of ranges, and it has worked very well. It actually works a bit easier in PostgreSQL than Oracle as you can use ORDER BY and LIMIT to get the appropriate row without the inline view required by Oracle's ROWNUM-based method, but that's really a matter of less wordy code rather than performance or functionality. – David Aldridge Nov 20 '12 at 09:24
@PeterÅkerlund. I find this solution far more straightforward and easier to maintain than you intended solution. In your solution you need a lot of code just to try to keep the data in your table consistent. Every insert, update and delete on that table will potentially break your application. – Rene Nov 20 '12 at 09:32
@Rene. When putting it that way you have almost convinced me to go change to storing the boundary only once. I have had bad experience of supporting of really bad implementation of the one boundary solutions and are therefor reluctant to implement it myself. – Peter Å Nov 20 '12 at 10:03
On second thoughts there should be no need for a upper end value. To determine the price for a given amount: Find the highest start value that is smaller than the amount and that will give you your price. That's all. – Rene Nov 20 '12 at 14:14

score 2 · Answer 2 · answered Nov 20 '12 at 08:21

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.

answered Nov 20 '12 at 08:21

Kirill Leontev

10,641
7
43
49

Interesting solution but I don't think that his is the way to go for us. I don't have any experience with fast-refreshed materialized views so can you please explain to me what will happen with the data in the table when the check constraint on the materialized view fails? – Peter Å Nov 20 '12 at 08:26
the statement would be rolled back – Kirill Leontev Nov 20 '12 at 08:27
In other word you mean that refresh of the materialized view will occuure in the same transaction as the updating of the table? – Peter Å Nov 20 '12 at 08:33
1

I misinformed you. The mat view is refreshed when you issue a commit statement, thus, if mat view constraint is violated, the whole transaction is rolled back. – Kirill Leontev Nov 20 '12 at 08:44

Damien_The_Unbeliever · Answer 3 · 2012-11-20T08:41:57.757

One way you can implement this is with mutually referencing foreign keys.

For this to work, you tend to need a database that supports MERGE statements or deferred constraints, and that UNIQUE constraints allow only a single NULL (or some workaround for this).

What you do is first switch to representing your ranges using a semi-open interval. You do this so that the end of one interval can be a foreign key reference to another rows start, and vice versa.

If I use dialect anywhere, it's likely to be TSQL, not Oracle, because that's what I'm used to, but the same concepts should apply

You create a table that looks like this:

CREATE TABLE T (
    PRICE_CODE int not null,
    START_RANGE decimal(10,2) null,
    END_RANGE decimal(10,2) null,
    constraint UQ_T_START UNIQUE (PRICE_CODE,START_RANGE),
    constraint UQ_T_END UNIQUE (PRICE_CODE,END_RANGE),
    constraint FK_T_PREV FOREIGN KEY (PRICE_CODE,START_RANGE) references T (PRICE_CODE,END_RANGE),
    constraint FK_T_NEXT FOREIGN KEY (PRICE_CODE,END_RANGE) references T (PRICE_CODE,START_RANGE),
    constraint CK_T_SANERANGE CHECK (START_RANGE < END_RANGE)
)

By only allowing a single row to have a NULL START_RANGE, only one row can represent the lowest range. Similarly, for END_RANGE and the highest range. All rows in between have to references their previous and next range rows.

You need deferred constraints or MERGE statements, since in order to, for example, insert a new row at the end, you need to both insert this row (referencing the previous row), and update the previous row (to reference the new row) for all of the constraints to be met. That requires either an INSERT and an UPDATE with no constraint checking occurring between the two, or a MERGE statement that can accomplish both in a single statement.

If you don't want to leave the lowest and highest ranges with undefined bounds, then just impose a rule that rows with a NULL START_RANGE or END_RANGE don't represent a valid range. But keep those rows in the table to allow the above structure to work.

Thank you for very interesting solution. Oracle version of UNIQUE constraint allows for for than one NULL but there are workarounds for that problem. A slight problem with this is in my opinion the semi opened intervals that have to be solved when querying in one way or another. — Peter Å, Nov 20 '12 at 08:54
@PeterÅkerlund - it just generally means doing `column <= value and value < column` (or similar comparisons) rather than using `BETWEEN` or always using `<=`. — Damien_The_Unbeliever, Nov 20 '12 at 08:55
Ok, I guess when giving it some more though it should be quite straightforward. — Peter Å, Nov 20 '12 at 09:04

Implementing table level check constraint

3 Answers3

Linked