Oracle: constraint check number range collision

Question

is there any way to check empty intersection of number range by constraint? Example:

CREATE TABLE "AGE_CATEGORIES" (
  "AGE_CATEGORY_ID" CHAR(2 BYTE) NOT NULL PRIMARY KEY,
  "NAME" NVARCHAR2(32) NOT NULL,
  "RANGE_FROM" NUMBER(*,0) NOT NULL,
  "RANGE_TO" NUMBER(*,0) NOT NULL,

  CONSTRAINT "UK_AGE_CATEGORIES_NAME" UNIQUE ("NAME"),
  CONSTRAINT "CHK_AGE_CATEGORIES_RANGE_COLLISION" CHECK (
    ???
  ) ENABLE
);

Question marks in the code above means something like:

(SELECT COUNT("AGE_CATEGORY_ID")
   FROM "AGE_CATEGORIES" AC
  WHERE "RANGE_FROM" < AC."RANGE_TO"
    AND "RANGE_TO" > AC."RANGE_FROM") = 0

So I need to check if new age category has no intersection with any other interval stored in this table. Is it possible?

score 2 · Accepted Answer · answered Mar 23 '15 at 20:22

2

It can be done, but involves creating materialized views with constraints - see my blog post. However this approach would need to be carefully considered as it could be a performance hit. In reality this sort of logic is not checked via constraints, only via procedural code in APIs or triggers.

answered Mar 23 '15 at 20:22

Tony Andrews

129,880
21
220
259

Ouch, ok, it's not necessary to do it this way, I'll check it by trigger or something ;-). Thanks. – Honza Mar 23 '15 at 21:31

Oracle: constraint check number range collision

1 Answers1