List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:November 4 2007 8:10pm
Subject:Re: Foreign keys on non-unique columns (problem)
View as plain text  
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

Thread
Foreign keys on non-unique columns (problem)Yves Goergen3 Nov
  • Re: Foreign keys on non-unique columns (problem)Yves Goergen3 Nov
  • Re: Foreign keys on non-unique columns (problem)Martijn Tonies4 Nov
    • Re: Foreign keys on non-unique columns (problem)Yves Goergen4 Nov