Received: from chronozon.artofdns.com (cr466046-a.etob1.on.wave.home.com [24.42.158.249]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3IKn3u81412 for ; Wed, 18 Apr 2001 16:49:04 -0400 (EDT) (envelope-from stef@chronozon.artofdns.com) Received: from devil.hades (devil.hades [192.168.2.6]) by chronozon.artofdns.com (Postfix) with SMTP id 76D6FC62A; Wed, 18 Apr 2001 16:51:31 -0400 (EDT) Content-Type: text/plain; charset="iso-8859-1" From: Stef Telford Organization: Hades Inc To: Stephan Szabo Subject: Re: breakage in schema with foreign keys between 7.0.3 and 7.1 Date: Wed, 18 Apr 2001 16:39:01 -0400 X-Mailer: KMail [version 1.2] Cc: pgsql-sql@postgresql.org References: In-Reply-To: MIME-Version: 1.0 Message-Id: <01041816390109.00282@devil.hades> Content-Transfer-Encoding: 8bit X-Archive-Number: 200104/315 X-Sequence-Number: 2671 Stephan Szabo wrote: > Hmm, don't know why it's not in changelog, but the spec requires that > the target fields of a foreign key constraint are themselves constrained > by a unique or primary key constraint. maybe its time for me to go and re-read the changelog with a fine tooth comb (it has been known for me to be blind to the obvious before and if this is the case then i more than apologise :) > 7.0 didn't actually check this, > but 7.1 does. The reason for this is because while 7.0 would let you > specify such a constraint, it wouldn't really work entirely properly > if the field wasn't actually unique. You'll need a unique constraint > on client.client_id. hhrrm. the only problem with -that- is that client_id by itself is not unique, but in conjunction with order_id it is. order_id is wholly unique. maybe i should jst drop the foreign key on client_id then, although i did want to use referential integrity on the client_id on an insert. although now i think about this, the criteria for having the changes on client_id cascading are totally gone and i could (read will) jst use a 'references' column. in short, thank you, i have jst figured out what an idiot i have been (again i hear you all say ;) many thanks and good work on postrgresql 7.1, it seems to be quite a bit quicker (and praise the lord for outer joins =) stefs