Re: multi column foreign key for implicitly unique columns

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