Re: Can arrays reference primary keys in CREATE TABLE?

Richard Huxton wrote:

>On Friday 09 May 2003 10:21 pm, Filipe Bonjour wrote:
>
>
>>Hi,
>>
>>I'm a rather new SQL user, and I found interesting that you can
>>use "references" to make sure that a value inserted in a table is a
>>primary key from another table. Something like:
>>
>>
>
>Yep - referential integrity is vital.
>
I used a trigger to get referential integrity.
So, on update or insert, it fires the function that's going to read the
new array and check if really exists on the other table

>
>
>
>>However, I'd like to define a table containing an array, and that
>>all elements of the array be a primary key from another table, so I
>>tried:
>>
>>create table tb (
>> a_ref integer[] references ta(a_id)
>> );
>>
>>But that doesn't work, I think because an array of integers can't
>>be directly compared to an integer.
>>
>>
>
>Don't use arrays, use another table, e.g.
>
>CREATE TABLE tb_items (
> tb_ref integer REFERENCE tb,
> ta_ref integer REFERENCES ta,
> PRIMARY KEY (tb_ref,ta_ref)
>);
>
>
>

--
Luis Sousa
Especialista de Informática
Gabinete de Gestão de Informação, ext: 7837
Campus de Gambelas
Universidade do Algarve, tel: 289800900