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