Most efficient way for creating unique index on Oracle if dates between two columns intersect

Question

Let's assume I have a table:

CREATE TABLE "STACK_OVERFLOW_QUESTION"
  (
    "SSN" VARCHAR2(13 BYTE) NOT NULL ENABLE,
    "IRN" VARCHAR2(11 BYTE) NOT NULL ENABLE,
    "BEGIN_DATE" DATE,
    "END_DATE" DATE
  )

I need a unique index which will enforce a policy where there cannot be intersection between the intervals from BEGIN_DATE to END_DATE having the same SSN and IRN. For example, let' assume there are these rows in the table:

|     SSN    |     IRN     |  BEGIN_DATE  |  END_DATE   |
|------------|-------------|--------------|-------------|
| 565654     | 154646678   | 01/01/2010   | 01/02/2010  | - (first row inserted) OK
| 565654     | 154646678   | 03/04/2010   | 20/04/2010  | - (second row inserted) OK
| 565654     | 154646678   | 28/01/2010   | 13/02/2010  | - (third row inserted) ERROR

Unique index is violated in the third row. Because intervals from 28/01/2010 to 13/02/2010 intersects with 01/01/2010 and 01/02/2010.

What is the most efficient way for handling this?

Answer 1

Have a look at my answer to this question: Adding constraints using subquery from other table

The basic idea is to write a query that identifies and counts the bad rows. Turn that query into a materialized view, and add a check constraint that fails whenever the nr of bad rows is greater than 0.

A query for your table would look something like this:

select count(*) as bad_rows
  from stack_overflow_question a
  join stack_overflow_question b using(ssn, irn)
 where a.rowid < b.rowid 
   and (   a.begin_date between b.begin_date and nvl(b.end_date, a.begin_date)
        or b.begin_date between a.begin_date and nvl(a.end_date, b.begin_date)
       );