X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B7C7E475EDF for ; Wed, 14 May 2003 12:46:16 -0400 (EDT) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with ESMTP id 91655-06 for ; Wed, 14 May 2003 12:46:15 -0400 (EDT) Received: from smtp.ualg.pt (smtp.ualg.pt [193.136.224.8]) by postgresql.org (Postfix) with ESMTP id 18744475EC9 for ; Wed, 14 May 2003 12:46:15 -0400 (EDT) Received: from localhost (smtp.ualg.pt [127.0.0.1]) by smtp.ualg.pt (Postfix) with ESMTP id 6EDA94BC54; Wed, 14 May 2003 17:46:16 +0100 (WEST) Received: from smtp.ualg.pt ([127.0.0.1]) by localhost (smtp.ualg.pt [127.0.0.1]) (amavisd-new, port 10024) with SMTP id 30663-09; Wed, 14 May 2003 17:46:13 +0100 (WEST) Received: from ualg.pt (corisco.uceh.ualg.pt [10.10.20.126]) by smtp.ualg.pt (Postfix) with ESMTP id 57CEA4BDB3; Wed, 14 May 2003 17:46:13 +0100 (WEST) Message-ID: <3EC272D9.5050203@ualg.pt> Date: Wed, 14 May 2003 17:46:17 +0100 From: Luis Sousa User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021226 Debian/1.2.1-9 X-Accept-Language: en-pt MIME-Version: 1.0 To: Richard Huxton Cc: Filipe Bonjour , pgsql-sql@postgresql.org Subject: Re: Can arrays reference primary keys in CREATE TABLE? References: <200305141631.48642.dev@archonet.com> In-Reply-To: <200305141631.48642.dev@archonet.com> Content-Type: text/plain; charset=iso-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/287 X-Sequence-Number: 13452 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