Re: The use of arrays as foreign key

On Tue, 20 Nov 2001, Mourad EL HADJ MIMOUNE wrote:

> Hi,
> I'am trying to use foreign key constraint on column of type int[] (
> array ) without success.
> Exemple:
>
> Create table course (id serial, name varchar);
> Create table student (id serial, name varchar, course int[3], Constraint
> f_key Foreign key (course) references course (id));
>
> plib_gene=# insert into course (name) values ('math');
> INSERT 600730 1
> plib_gene=# insert into course (name) values ('info');
> INSERT 600731 1
> plib_gene=# insert into course (name) values ('phys');
> INSERT 600732 1
> plib_gene=# insert into course (name) values ('chimie');
> INSERT 600733 1
> plib_gene=# insert into course (name) values ('sport');
> INSERT 600734 1
> plib_gene=# insert into student (name, course) values ('mourad', '{1,2,3}');
> ERROR: Unable to identify an operator '=' for types 'int4' and '_int4'
> You will have to retype this query using an explicit cast

Foreign keys need to be on comparable types (ie = is defined between them
for our implementation -- which is probably weaker than it'd need to be
for complete safety). Int array and int aren't comparable. In general
both sides of the foreign key should be the same base type.

> Why we can't use OID as a foreign key?
IIRC, there was a bug due to me not understanding how to get the system
attributes which I think Tom may have fixed for 7.2.