Re: Foreign Keys With Arrays

Ketema Harris writes:

> If I have a column that is of type int4[] (array of integers) is it possible
> to require that each integer within that array is a foreign key?

I'm afraid you'll have to define a custom check-constraint to do that.

E.g.

--8<---------------cut here---------------start------------->8---
scratch=# create table foo (a int);
CREATE TABLE
scratch=# insert into foo values (1);
INSERT 6856486 1
scratch=# insert into foo values (2);
INSERT 6856487 1
scratch=# create table bar (a int[]);
CREATE TABLE
scratch=# create function valid_array(a int4[]) returns boolean as $$
scratch$# begin
scratch$# for i in array_lower(a, 1)..array_upper(a,1) loop
scratch$#    if not exists (select 1 from foo where foo.a = a[i]) then
scratch$#        return 'f';
scratch$#    end if;
scratch$# end loop;
scratch$# return 't';
scratch$# end $$ language plpgsql;
CREATE FUNCTION
scratch=# alter table bar add constraint foo check(valid_array(a));
ALTER TABLE
scratch=# insert into bar values ('{1,2}');
INSERT 6856494 1
scratch=# insert into bar values ('{1,3}');
ERROR:  new row for relation "bar" violates check constraint "foo"
scratch=#
--8<---------------cut here---------------end--------------->8---

regards,
Andreas
--