Re: challenging constraint situation - how do I make it

Kenneth Downs wrote:
> Alban Hertroys wrote:
> The approach I tried was to have a "range" or "interval" type.  You
> place a column into a table named "resv_date" or whatever and it would
> expand the definition into two columns, you'd get resv_date_beg and
> resv_date_end.  If you declared the "resv_date" column a primary key
> column, it would build trigger code to detect overlaps and nesting and
> reject those.
>
> As I said, defining behavior and implementing it was not hard.  I even
> had foreign keys into ranges that were "smart".  If the foreign key was
> a single column instead of two, it would satisfy RI if the single value
> was between the interval values in the parent table.

Been there, done that ;)

> The problem comes from the split-personality of the "resv_date" column.
> Sometimes its one column, sometimes its two.  This made writing the
> tools nasty and difficult, and I scratched it and (gasp!) did some
> validation in client code.

What's the benefit of allowing it to be only one column?

> I have it in mind to restore the feature, but in a different way.  The
> two columns should be defined separately, not as one, and then the
> second of the two gets a flag setting, like:
>
> column range_beg { primary_key: Y; }
> column range_end { primary_key: Y; range_from: range_beg; }
>
> The "range_from" setting ties one column to the other and should give me
> all the behavior I had without all of the confusion.  It would have
> three effects:
>
> 1)  Force range_end >= range_beg
> 2)  Convert the primary key into overlap/nest exclusion
> 3)  Allow a single column foreign key in another table to "know" that it
> should do a within match instead of an equality match

And making that one column of a composite type would be just the thing,
I thought somewhere at the start of this thread (Thanks for mentioning
"composite types", Florian, couldn't remember what they're called).

Cheers,

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //