Re: Help with array constraints

> >
> > 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.