From: | "Nigel J(dot) Andrews" <nandrews(at)investsystems(dot)co(dot)uk> |
---|---|
To: | Jason Hihn <jhihn(at)paytimepayroll(dot)com> |
Cc: | Richard Huxton <dev(at)archonet(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Help with array constraints |
Date: | 2003-04-03 16:18:43 |
Message-ID: | Pine.LNX.4.21.0304031709400.12600-100000@ponder.fairway2k.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, 3 Apr 2003, Jason Hihn wrote:
> > >
> > > What must I do?
> >
> > You might be able to add separate FK constraints (see CREATE
> > TABLE or ALTER
> > TABLE) for each array element.
> >
> > CREATE TABLE test (
> > ...
> > CONSTRAINT first_letter FOREIGN KEY (letter[0]) REFERENCES _test(id),
> > CONSTRAINT first_letter FOREIGN KEY (letter[1]) REFERENCES _test(id),
> > CONSTRAINT first_letter FOREIGN KEY (letter[2]) REFERENCES _test(id)
> > )
> >
> > Don't know if that will work - I'm wary of using arrays myself. I'm a bit
> > surprised that you can create a primary key on an array.
>
> Whoops, that's not actually in my application!
>
> > The other option is that you might need to alter your design.
> > Either three
> > separate fields or a separate table with (letter_index,
> > char_value) so you
> > only need the FK constraint on "char_value", and letter_index can be
> > constrained with a CHECK.
>
> Design altering is a BAD thing because I am attempting a port from a DBMS
> that supports arrays, and they are used quite extensively in places.
> Adjusting the schema would be to take a large hit, and the powers that be
> are already weary about doing the port in the first place. Seeing this was
> now supported a big joy for me, because it makes it so much easier.
Well that constraint reply didn't contain what I was expecting to see after I
started reading it so...
You probably want to write your own function(s) and install it(them) as
trigger(s).
create function letter_fk () returns trigger as '
declare
ind integer;
begin
for ind in array_dims(NEW.letters) loop
perform 1 from _test where id = NEW.letters[ind];
if not found then
raise exception ''My foriegn key constraint
violation'';
end if;
end loop;
return NEW;
end;
' as language 'plpgsql';
create trigger my_array_fkey
before insert
on test
for each row execute procedure letter_fk();
Or something like that anyway. Also that's only a start.
--
Nigel J. Andrews
From | Date | Subject | |
---|---|---|---|
Next Message | Jason Hihn | 2003-04-03 17:11:52 | Re: Help with array constraints |
Previous Message | Stephan Szabo | 2003-04-03 16:15:50 | Re: Help with array constraints |