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. # #================================================== [EMAIL PROTECTED] #
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
