Re: Referencing "less-unique" foreign keys

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

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.