Implementing table level check constraint

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.

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.

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.