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