Received: from finch-post-11.mail.demon.net (finch-post-11.mail.demon.net [194.217.242.39]) by postgresql.org (Postfix) with ESMTP id F1CE047612B for ; Mon, 25 Feb 2002 12:42:31 -0500 (EST) Received: from lfix.demon.co.uk ([158.152.59.127] helo=linda.lfix.co.uk) by finch-post-11.mail.demon.net with esmtp (Exim 3.34 #1) id 16fP93-000AGx-0B; Mon, 25 Feb 2002 17:42:37 +0000 Received: from localhost ([127.0.0.1] helo=localhost.localdomain ident=olly) by linda.lfix.co.uk with esmtp (Exim 3.34 #1 (Debian)) id 16fPD6-0005bZ-00; Mon, 25 Feb 2002 17:46:48 +0000 Subject: Re: About referential integrity. From: Oliver Elphick To: BOURIAUD Cc: pgsql-sql@postgresql.org In-Reply-To: <200202251503.g1PF3vn21366@necker.ac-rouen.fr> References: <200202251503.g1PF3vn21366@necker.ac-rouen.fr> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Evolution/1.0.2 Date: 25 Feb 2002 17:46:47 +0000 Message-Id: <1014659208.13241.6853.camel@linda> Mime-Version: 1.0 X-Archive-Number: 200202/362 X-Sequence-Number: 6784 On Mon, 2002-02-25 at 15:01, BOURIAUD wrote: > Since I fear I'm not clear in the above, let me show you an example. > > > Let's say I create two tables as shown below : > > create table t1 > ( > key1 serial PRIMARY KEY, > field1 varchar(30) > ); > > create table t2 > ( > key2 serial not null PRIMARY KEY, > key1 int4 references t1, > field2 varchar(30) > ); > > As far as I know, t2 and t1 are linked together. If I want to select rows > that belong to both tables, I would have to write something like this : > select * from t1, t2 where t1.key1 = t2.key1; > > I think this is quite redundant, since I've created my tables so as to make > sure that they would respect what is in the select above !!!! > > > So, why souldn't I be abble to write : > select * from t1, t2; > > and have the backend returns me only the rows that have t1.key1 = t2.key2 ??? That is not what SQL does. The purpose of referential integerity is to ensure that a value in one table corresponds to a row in another. It operates on database changes; it does not extend to select queries. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Peace I leave with you, my peace I give unto you; not as the world giveth, give I unto you. Let not your heart be troubled, neither let it be afraid." John 14:27