Thread: 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

> 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_tabWHERE pk = NEW.array_value[_i];   IF NOT FOUND THEN     RAISE EXCEPTION '..........';   END IF; END LOOP;
RETURNNEW;
 
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