Message-ID: <41235A90.1080300@Yahoo.com>
Date: Wed, 18 Aug 2004 09:33:04 -0400
From: Jan Wieck <JanWieck@Yahoo.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.7.1) Gecko/20040707
MIME-Version: 1.0
To: Josh Berkus <josh@agliodbs.com>
Cc: Markus Bertheau <twanger@bluetwanger.de>, olly@lfix.co.uk,
	pgsql-sql@postgresql.org
Subject: Re: multi column foreign key for implicitly unique columns
References: <1092734724.2627.4.camel@dicaprio.akademie1.de>
	<1092735576.28365.48.camel@linda>
	<1092736197.2627.12.camel@dicaprio.akademie1.de>
	<200408171945.15663.josh@agliodbs.com>
In-Reply-To: <200408171945.15663.josh@agliodbs.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

On 8/17/2004 10:45 PM, Josh Berkus wrote:

> Markus,
> 
> Hey, I see you figured out a workaround to writing a trigger for this.  Let's 
> see if we can make it work.
> 
> ERROR:  there is no unique constraint matching given keys
>         for referenced table "objects"
> 
> The reason for this is that CASCADE behavior gets quite odd when there is an 
> FK reference to a non-unique column.   We used to allow it, in 7.1, and I was 
> responsible for a number of bug reports that led to us disallowing it.   It 
> should be theoretically implementable and relationally sound but will require 
> a *lot* of troubleshooting to make work.   So far, nobody's really interested 
> enough.

SQL92 4.10 Integrity constraints:

     ...

     In the case that a table constraint is a referential constraint,
     the table is referred to as the referencing table. The referenced
     columns of a referential constraint shall be the unique columns of
     some unique constraint of the referenced table.

     ...

Meaning that not enforcing the uniqueness of those columns isn't an 
option. PostgreSQL is currently happy with a UNIQUE constraint that 
covers those columns in any order, which is to the letter spec 
compliant. "Really interested" will not do here.


Jan

> 
> However, you have an easy way out:
> 
> ALTER TABLE objects ADD CONSTRAINT obj_unq_2 UNIQUE (name, class_name);
> 
> This will add the unique constraint that Postgres wants without changing your 
> data at all.
> 


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #