X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 5132E5E46F1
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Aug 2004 11:26:58 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 94496-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 17 Aug 2004 14:26:56 +0000 (GMT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id 7D7FE5E46CD
	for <pgsql-sql@postgresql.org>; Tue, 17 Aug 2004 11:26:53 -0300 (ADT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 2087D351BB; Tue, 17 Aug 2004 07:26:56 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 1F08334DEF; Tue, 17 Aug 2004 07:26:56 -0700 (PDT)
Date: Tue, 17 Aug 2004 07:26:56 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Richard Huxton <dev@archonet.com>
Cc: Markus Bertheau <twanger@bluetwanger.de>,
	pgsql-sql@postgresql.org
Subject: Re: multi column foreign key for implicitly unique columns
In-Reply-To: <4121D55D.1040105@archonet.com>
Message-ID: <20040817072443.S60684@megazone.bigpanda.com>
References: <1092734724.2627.4.camel@dicaprio.akademie1.de>
	<4121D55D.1040105@archonet.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200408/167
X-Sequence-Number: 18664


On Tue, 17 Aug 2004, Richard Huxton wrote:

> Markus Bertheau wrote:
> > Hi,
> >
> > PostgreSQL doesn't allow the creation of a foreign key to a combination
> > of fields that has got no dedicated unique key but is unique nonetheless
> > because a subset of the combination of fields has a unique constraint.
> [snip example]
> > Is this on purpose? I think the foreign key should be allowed. Creating
> > an extra unique key only has a negative impact on performance, right?
>
> As you say, the uniqueness is guaranteed so there's no good reason why
> it couldn't be made to work. It's probably more of an implementation
> issue. Unique constraints are implemented with an index, so I'm guessing

No, actually, it's that the SQL92 (at least) spec says explicitly that
there must be a unique constraint across all of the columns specified, not
merely across a subset.

"then the set of column names of that <reference column list> shall be
equal to the set of column names in the unique columns of a unique
constraint of the referenced table."