Re: Help with array constraints

On Thursday 03 Apr 2003 3:48 pm, 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?

You've asked it to compare the whole array of single-chars with a single-char.
PG says it can't do that.

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

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.

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.

--
Richard Huxton