From: Jan Wieck <JanWieck@Yahoo.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: Markus Bertheau <twanger@bluetwanger.de>
Cc: olly@lfix.co.uk
Cc: pgsql-sql@postgresql.org
Subject: Re: multi column foreign key for implicitly unique columns
Date: Wed, 18 Aug 2004 09:33:04 -0400
Message-ID: <41235A90.1080300@Yahoo.com> (raw)
In-Reply-To: <200408171945.15663.josh@agliodbs.com>
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>
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 #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: JanWieck@Yahoo.com, josh@agliodbs.com, twanger@bluetwanger.de, olly@lfix.co.uk
Subject: Re: multi column foreign key for implicitly unique columns
In-Reply-To: <41235A90.1080300@Yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox