Обсуждение: 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