Re: Arrays and foreign keys

On Thu, 10 Aug 2000, Chris Bitmead 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).  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.