Prevent Overlapping In Range Tables

In Joining On Ranges, I demonstrated that range tables are a practical replacement for attribute columns. As a rule, ranges should not overlap. (Perhaps there are exceptions.) Here’s why, and also what to do to prevent overlapping values.

Overlapping ranges cause too many rows of the range table to join to a single row of another table, which in turn causes too many rows in the result set. For example, assume the following range table.

FROMITEMNUMBER  THRUITEMNUMBER  ITEMCATEGORY
    134             134999           1
    1341            134199           1
    488             488999           2
    2               299999           3

Item 134120 would join to the first two rows of the table.

The traditional IBM midrange way to keep the unwanted second row out of the range table would be to include the appropriate logic in the file maintenance program. The problem with that approach is that someone may decide to use something other than the file maintenance program to update the range table. That something could be SQL. It could even be a simple Update Data (UPDDTA) command.

It’s better to make the database itself prevent the inclusion of bad data. Preventing overlapping ranges is a simple matter of one check constraint and two triggers.

The check constraint ensures that the beginning of the range is not after the end of the range.

create table ItemCategories
   for system name ItemCateg
  (FromItemNumber for column FromItem   char(6),
   ThruItemNumber for column ThruItem   char(6),
   ItemCategory   for column Category   char(1),
  primary key (FromItemNumber),
  constraint ItemCategoryRange1
       check (FromItemNumber <= ThruItemNumber) 
  );

If you try to insert or update a row such that the beginning value is greater than the ending value, the system will rudely refuse to change the table, and will send vulgar message SQL0545:

INSERT, UPDATE, or MERGE not allowed by CHECK constraint.
 Cause . . . . . :   The value being inserted or updated does not 
 meet the criteria of CHECK constraint
ITEMCATEGORYRANGE1. The operation is not allowed.

The triggers run before inserts and updates to verify that the new data will not overlap existing data. This is not a difficult verification.

create or replace trigger ItemCategoriesBI
  before insert on ItemCategories
  referencing new row as n
for each row mode db2row
begin
   declare Counter integer default 0; 
   set Counter =
         (select count(*)
            from ItemCategories as cat
           where n  .FromItemNumber <= cat.ThruItemNumber
             and cat.FromItemNumber <= n  .ThruItemNumber);
   if (Counter > 0) then
      signal sqlstate '75001'
         ('Items are already assigned to a category.');
   end if;
end;

create or replace trigger ItemCategoriesBU 
  before update on ItemCategories
  referencing old row as o new row as n
for each row mode db2row
begin
   declare Counter integer default 0;
   if n.FromItemNumber < o.FromItemNumber
   or n.ThruItemNumber > o.ThruItemNumber then
      set Counter =
         (select count(*)
            from ItemCategories as cat
           where cat.FromItemNumber <> o  .FromItemNumber
             and n  .FromItemNumber <= cat.ThruItemNumber
             and cat.FromItemNumber <= n  .ThruItemNumber);
      if (Counter > 0) then
         signal sqlstate '75002'
            ('Item numbers are already assigned.');
      end if;
   end if;
end;

The triggers count the ranges that overlap the values of the new range. For example, assume these existing rows.

FROMITEMNUMBER  THRUITEMNUMBER  ITEMCATEGORY
    134             134999           1
    488             488999           2

Assume I try to insert a range that fits inside the first range.

insert into ItemCategories values
('1341', '134199', 1)

The where clause of the insert trigger evaluates to this:

where '1341' <= '134999'  
  and '134'  <= '134199');

The query finds one such row. The trigger uses the SIGNAL command to send an escape message to the caller, which brutishly passes along the information through message SQL0723:

SQL trigger ITEMCATEGORIESBI in SOMELIB failed with SQLCODE 
-438 SQLSTATE 75001.
Cause . . . . . :   An error has occurred in a triggered SQL 
statement in trigger ITEMCATEGORIESBI in
schema SOMELIB.  The SQLCODE is -438, the SQLSTATE is 75001, 
and the message is Items are
already assigned to a category.

An update may also attempt to create an overlap.

update ItemCategories
  set row = ('4881', '488199', 5)
 where FromItemNumber = '134'

The WHERE clause of the update trigger evaluates to the following:

where '488' <> '134'  
  and '4881' <= '488999'  
  and '488' <= '488199')

The first condition prevents the trigger from counting the row that is being updated. The second and third conditions are the same ones used in the insert trigger.

As before, the system heartlessly responds with message SQL0723:

SQL trigger ITEMCATEGORIESBU in SOMELIB failed with SQLCODE 
-438 SQLSTATE 75002.
Cause . . . . . : An error has occurred in a triggered SQL 
statement in trigger ITEMCATEGORIESBU in
schema SOMELIB. The SQLCODE is -438, the SQLSTATE is 75002, 
and the message is Item
numbers are already assigned.

Yes, the system is callous and unforgiving, but better an offended user than a corrupt database, I say.

RELATED STORY

Joining On Ranges