List:General Discussion« Previous MessageNext Message »
From:Yves Goergen Date:November 3 2007 9:52pm
Subject:Foreign keys on non-unique columns (problem)
View as plain text  
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" <nospam.list@stripped>
Visit my web laboratory at http://beta.unclassified.de
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