Martijn van Oosterhout wrote:
On Tue, Aug 09, 2005 at 02:31:16PM +0200, Alban Hertroys wrote:
Hi all,
We migrated a database from version 7.3 something to 7.4.7 a while ago,
and ever since that time we can't make new foreign keys to a particular
table. The problem is that the primary key on that table is on two
columns that are unique together, but that only one of them should be
referenced from the other table.
Foreign keys have to reference a column that has only unique values.
This is what the SQL standard requires of FOREIGN KEYS. If your
localization_id in the localization table is unique, just add a UNIQUE
index, problem solved.
If localization_id is not unique but you really want foreign keys,
you'll have to create a table containing only localization_ids and have
both tables foreign key to that...
I was afraid that would be the only answer... It's the way I would have
solved it too - would I have the time.
SELECTs and UPDATEs aren't influenced by the change, but INSERTs and
DELETEs (w/ cascade) are. Am I right that this could be fixed
transparently (to our queries) by creating a few RULEs on localization
on INSERT and DELETE? That'd certainly save some time...
--
Alban Hertroys
alban@xxxxxxxxxxxxxxxxx
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
//Showing your Vision to the World//
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
