 | | | Subject: Foreign keys on non-unique columns (problem) | Subject: Foreign keys on non-unique columns (problem) 2007-11-03 - By Yves Goergen
Back
Hi,
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;
Which is supposed to mean the following: When I delete a keylist and
there's no remaining row with this keylist ID, then find the users that
are referencing it and set their AdditionalKeylist value to NULL so that
they doesn't keep an invalid reference.
The problem: When a keylist ID exists twice and I delete one of them,
the user's AdditionalKeylist value is set to NULL immediately, although
another keylist ID instance exists.
I have read through the MySQL documentation about foreign keys and
understand that referencing a non-unique column (i.e. not a "candidate
key") is not standard SQL and that InnoDB doesn't exactly do what I want
(it ignores the remaining relevant rows).
|
|
|