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).
>From SQLite (which doesn't currently enforce foreign keys) I have
learned a trigger that can do the same (haven't tested it yet, though),
but to extend it to that "look for other instances" check, I need it to
understand the WHEN part of my trigger, which seems to be commonly known
but unsupported (and undocumented) by MySQL 5.0. Also, MySQL requires
uncommonly high privileges to create a trigger which is not an option in
the field (I'm planning to release my application for use on common web
space).
I hope you understand my problem. There's two potential solutions which
both don't work for me. Is there a third? Can I create this kind of
referential integrity on the DBMS level at all? Is my design bad?
--
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
Visit my web laboratory at http://beta.unclassified.de
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
