From: Martijn Tonies
Date: November 4 2007 8:10pm
Subject: Re: Foreign keys on non-unique columns (problem)
List-Archive: http://lists.mysql.com/mysql/209875
Message-Id: <001d01c81f1e$ca437e80$9902a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: 7bit

Hi Yves,

> I have a problem with my foreign keys. I have the following two tables:
>
> CREATE TABLE "keylist" (
>     "KeylistId" INTEGER NOT NULL,
>     "UserId" INTEGER NOT NULL,
>     PRIMARY KEY (KeylistId, UserId));
>
> CREATE TABLE "user" (
>     "UserId" INTEGER NOT NULL PRIMARY KEY,
>     "AdditionalKeylist" INTEGER);
>
> A keylist stores multiple user IDs for each keylist ID. A user has a
> reference to one keylist to keep multiple additional keys. (My "key" is
> the same as a "user ID".)
>
> Now I have added this foreign key constraint:
>
> ALTER TABLE "user" ADD FOREIGN KEY ("AdditionalKeylist") REFERENCES
> "keylist" ("KeylistId") ON DELETE SET NULL;

This cannot work. The column in KEYLIST to which you are
pointing should have a unique value, that means either a primary
key or unique constraint.

Given that the constraint on KEYLIST means that you can have
multiple KEYLIST entries for each USERID value, how is a
foreign key constraint supposed to be pointing to a single entry
in KEYLIST? It cannot, unless you're referencing a unique (pair)
value.

What is it exactly that you want to store?

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com