Re: Help with array constraints

On Thu, 3 Apr 2003, Jason Hihn wrote:

> Two tables (simplified):
>
> CREATE TABLE _test (
> id CHAR(1),
> PRIMARY KEY(id)
> );
>
> INSERT INTO _test VALUES ('a');
> INSERT INTO _test VALUES ('b');
>
> CREATE TABLE test (
> letter CHAR(1)[3] NOT NULL REFERENCES _test(id)
> PRIMARY KEY(letter)
> );
>
> CREATE TABLE / PRIMARY KEY will create implicit index 'test_pkey' for table
> 'test'
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ERROR: Unable to identify an operator '=' for types 'character[]' and
> 'character'
> You will have to retype this query using an explicit cast
>
> Can someone please explain that in English? I want ALL the letter field
> values to be checked against what is in the _test table id field when a row
> is inserted. For example, 'a' and 'b' is in the _test table now, if I insert
> an 'a' or 'b' into test, it will suceed. If I insert a 'c' or 'd' it should
> fail.
>
> What must I do?

Probably write specialized triggers. As a note, if test is even marginally
large, the check for update/delete on _test is going to probably be
immensely painful unless you have an index on the individual elements of
the array rather than the array as a whole (AFAIK that'll index the
complete array, which means that it's probably not terribly useful for
searching for subelements).

If you only want to do insert/update on test time checks (and not worry
about update/delete from _test) this becomes somewhat easier. You can
make a function to do the checks against _test for each element value and
use it in a check constraint.