From: Luis Sousa <llsousa@ualg.pt>
To: Richard Huxton <dev@archonet.com>
Cc: Filipe Bonjour <fbonjour@urbanet.ch>
Cc: pgsql-sql@postgresql.org
Subject: Re: Can arrays reference primary keys in CREATE TABLE?
Date: Wed, 14 May 2003 17:46:17 +0100
Message-ID: <3EC272D9.5050203@ualg.pt> (raw)
In-Reply-To: <200305141631.48642.dev@archonet.com>
References: <pan.2003.05.09.21.21.24.908732@urbanet.ch>
<200305141631.48642.dev@archonet.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: llsousa@ualg.pt, dev@archonet.com, fbonjour@urbanet.ch
Subject: Re: Can arrays reference primary keys in CREATE TABLE?
In-Reply-To: <3EC272D9.5050203@ualg.pt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox