Skip site navigation (1) Skip section navigation (2)

Re: multi column foreign key for implicitly unique columns

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: Markus Bertheau <twanger(at)bluetwanger(dot)de>, olly(at)lfix(dot)co(dot)uk,pgsql-sql(at)postgresql(dot)org
Subject: Re: multi column foreign key for implicitly unique columns
Date: 2004-08-18 13:33:04
Message-ID: 41235A90.1080300@Yahoo.com (view raw or flat)
Thread:
Lists: pgsql-sql
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 #

In response to

Responses

pgsql-sql by date

Next:From: Markus BertheauDate: 2004-08-18 13:49:21
Subject: Re: multi column foreign key for implicitly unique columns
Previous:From: Greg Sabino MullaneDate: 2004-08-18 11:47:41
Subject: Re: SQL Challenge: Arbitrary Cross-tab

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group