Subject: Re: Foreign keys on non-unique columns (problem) 2007-11-04 - By Martijn Tonies
Back
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
