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?
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.
"Timothy H. Keitt" wrote:
>
> I get exactly the same behavior; it would be really helpful if foreign key
> constraints were available for array types!
>
> Tim
>
> Kaare Rasmussen wrote:
>
> > Seems that it's not possible to combine arrays and foreign keys ?
> >
> > CREATE TABLE table1 (
> > fld1 integer NOT NULL,
> > number integer,
> > level integer,
> > PRIMARY KEY (fld1)
> > );
> >
> > CREATE TABLE table2 (
> > pkey integer NOT NULL,
> > arvar integer[],
> > PRIMARY KEY (pkey),
> > FOREIGN KEY (arvar) REFERENCES table1(fld1)
> > );
> >
> > This works, but the following insert complains that
> >
> > ERROR: Unable to identify an operator '=' for types 'int4' and '_int4'
> > You will have to retype this query using an explicit cast
> >
> > --
> > Kaare Rasmussen --Linux, spil,-- Tlf: 3816 2582
> > Kaki Data tshirts, merchandize Fax: 3816 2582
> > Howitzvej 75 Åben 14.00-18.00 Email: kar@webline.dk
> > 2000 Frederiksberg Lørdag 11.00-17.00 Web: www.suse.dk
>
> --
> Timothy H. Keitt
> National Center for Ecological Analysis and Synthesis
> 735 State Street, Suite 300, Santa Barbara, CA 93101
> Phone: 805-892-2519, FAX: 805-892-2510
> http://www.nceas.ucsb.edu/~keitt/