Foreign keys on array elements

Hi,

Can you put a foreign key constraint on an array column that says that
each element of the array must match a primary key?

If not, is this a TODO perhaps?

Chris

Hello,

Using array values for foreign key is very special. I not sure, so all
people need it. More interesting is CHECK on array. But you can write
simply trigger.

CREATE OR REPLACE FUNCTION check_() RETURNS TRIGGER AS $$
DECLARE _v integer;
BEGIN
FOR _i IN array_lower(NEW.array_value,1) ..
array_upper(NEW.array_value,1)
LOOP
PERFORM 1 FROM some_tab WHERE pk = NEW.array_value[_i];
IF NOT FOUND THEN
RAISE EXCEPTION '..........';
END IF;
END LOOP;
RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER foo BEFORE INSERT OR UPDATE ON ...
FOR EACH ROW EXECUTE PROCEDURE check_();

Regards
Pavel Stehule

On Wed, 20 Apr 2005, Christopher Kings-Lynne wrote:

Hi,

Can you put a foreign key constraint on an array column that says that
each element of the array must match a primary key?

Not currently, because foreign keys are between directly comparable
things.

If not, is this a TODO perhaps?

Maybe. It's been discussed before IIRC. Doing the referential actions
might get tricky, and you'd often want to index so that finding the
individual array elements isn't expensive.

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:

If not, is this a TODO perhaps?

Maybe. It's been discussed before IIRC. Doing the referential actions
might get tricky, and you'd often want to index so that finding the
individual array elements isn't expensive.

Checking PK deletions efficiently would be impossible, at least without
using index types that aren't considered mainstream yet ... I can't see
doing this until GIST is in a better state ...

regards, tom lane