Re: Help with array constraints

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