Re: multi column foreign key for implicitly unique columns

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(at)Yahoo(dot)com #