1

Is there a way to create a unique index on two integer fields, such that a given integer can only appear once in one of those fields? I.e.

+------+-------+
| Left | Right |
+------+-------+
|    1 |     2 |
|   *2*|     3 | < INVALID
+------+-------+

EDIT: note, the left..right range should still be able to contain other ranges in their entirety:

+------+-------+
| Left | Right |
+------+-------+
|    1 |     4 |
|    2 |     3 | < VALID CHILD
+------+-------+

I'm using Rails 4 and Postgres 9.2.

PJSCopeland
  • 2,818
  • 1
  • 26
  • 40
  • 1
    I think this could be achieved using the new range data type and an exclusion constraint. –  Nov 10 '13 at 21:11
  • 1
    an exclusion constraint would stop 4 and 5 being added if a record existed with Left=3 and Right=6 – Gareth Nov 11 '13 at 00:40
  • [This related answer](http://stackoverflow.com/questions/8016776/can-postgresql-have-a-uniqueness-constraint-on-array-elements/8017013#8017013) provides a solution for a similar case (trigger + additional table + unique constraint). Look to the second chapter ("Solution for the dark side"). – Erwin Brandstetter Nov 11 '13 at 02:34

1 Answers1

1

Not with a simple b-tree unique index. You can require distinct pairs - and with a functional index, can ignore pair ordering - but the index gets one leaf per tuple and there really isn't a way around that.

As horse says, you'd need to turn your values into a range type and then use an exclusion constraint to enforce it. Or just use a trigger - slower, but easy.

Edit: Looks like your original question completely misstated the goal. You're not interested in individual values at all, you're treating the pairs as ranges. You appear to want to:

  • Exclude ranges that partially overlap; but
  • Permit ranges that wholly overlap (one contains the other) or do not overlap at all.

It is possible you might be able to achieve this with some work using exclusion constraints - but I somewhat doubt it, since exclusion constraints aren't supposed to allow any overlap. Again, you're probably looking at a trigger-based solution here.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778