Re: Unique Keys

Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net> wrote in message news:<nm4g72-cr6.ln1_at_pluto.downsfam.net>...
> Mikito Harakiri wrote:
> > select 0 from Interval i1, Interval i2
> > where i2.x between i1.x and i1.y
> > or i2.y between i1.x and i1.y =
> > ={}

Actually, the selfjoin is symmetrical and the equation could be simplified to

select 0 from Interval i1, Interval i2
where i2.x between i1.x and i1.y
= {}

Indeed, let i2.x be between i1.x and i1.y. Where i2.y might be? Well, it is certainly either greater, or smaller than i1.y. If it's greater, then
i1.y is between i2.x and i2.y, otherwise, i2.y is between i1.x and i1.y!

Now it's as succinct as Overlap operator, but I would prefer to write it little bit more explicitly as

select 0 from Interval i1, Interval i2
where i2.x > i1.x and i2.x < i1.y
= {}

First, is would define precisely if intervals touching each other are allowed or not. (In Overlap operator case I would have to consult the manual).

Second, it would eliminate a bug hidden in the previous versions: i.x is always between i.x and i.y! We have to explicitly specify i1 be distinct from i2.

> yes, but that is not a unique constraint. Is there anyway at all to imagine
> it as a declarative unique constraint?

This question, although perfectly valid, looks tough. Could interval domain be mapped into some fancy custom made domain such that interval overlapping corresponds to identity? Received on Sat Nov 27 2004 - 19:08:24 CET