Re: Referencing "less-unique" foreign keys

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@magproductions.nl

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//