Re: Arrays and foreign keys

Stephan Szabo wrote:
> > This is an interesting point. Originally postgres integrity rules were
> > based on a very general rules system where many things were possible to
> > specify. I'm curious about the more recent addition of referential
> > integrity to postgres (I know little about it), why it is such a
> > specific solution and is not based on the more general postgres rules
> > system?
>
> Because unfortunately the SQL spec for referential integrity cannot really
> be implemented in the current rules system (or at least not in a way that
> is terribly nice).

So it wasn't feasible to extend the current rules system to support
these oddities, instead of implementing the specific solution?

> One problem is the fact that they need the option to
> be deferred to end of transaction (which we still have problems with now),
> plus I'm not sure that MATCH PARTIAL with referential integrity would be
> possible with the rewrites without having 2^(number of key elements) rules
> per action per constraint (that's the not terribly nice part). And there
> are rules about not letting a piece of data get multiply changed due to
> circular dependencies that you'd need to work in as well. All in all,
> it's a mess.
>
> > There are some functions somewhere in contrib that allow you to say
> > whether something is somewhere within an array, which is generally
> > useful for an ODBMS style data model and also the example below. Ideally
> > it could somehow be linked into integrity checks.
> For now, you should be able define the element in array as the equality
> operator between integer and array of integers which would probably do
> it.
>
> The spec generally says that the referenced and referencing values should
> be equal (well, there are exceptions more NULLs in various cases). We'd
> have to decide whether we'd want to extend that to be equal, except in the
> case that the referenced value is an array in which case we use in array
> instead. It'd probably be fairly easy probably to make the change
> assuming it's easy to tell if a column is an array.