Content-Type: text/plain;
  charset="utf-8"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Markus Bertheau <twanger@bluetwanger.de>, olly@lfix.co.uk
Subject: Re: multi column foreign key for implicitly unique columns
Date: Tue, 17 Aug 2004 19:45:15 -0700
User-Agent: KMail/1.4.3
Cc: pgsql-sql@postgresql.org
References: <1092734724.2627.4.camel@dicaprio.akademie1.de>
	<1092735576.28365.48.camel@linda>
	<1092736197.2627.12.camel@dicaprio.akademie1.de>
In-Reply-To: <1092736197.2627.12.camel@dicaprio.akademie1.de>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200408171945.15663.josh@agliodbs.com>

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.

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.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco