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/[EMAIL PROTECTED]