Re: Foreign keys on non-unique columns (problem)
On 04.11.2007 21:10 CE(S)T, Martijn Tonies wrote:
>> 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.
I know, how I've written further down.
> 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.
That's an interesting point. Actually, I'm not referencing a single row,
but a single value which can occur multiple times.
> What is it exactly that you want to store?
What I want to store is the "reference" on a key list ID that really
exists. And as soon as the key list ID does not exist any more (i.e.
because of the last occurence has been deleted), the "reference" on that
value (not row!) shall be set to NULL.
Regarding it this way, my hope to get this done with RDBMS means shrinks...
(Sorry for double sending, I hit the "Reply" button first...)
--
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]
Re: Foreign keys on non-unique columns (problem)
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]
Re: Foreign keys on non-unique columns (problem)
On 03.11.2007 22:52 CE(S)T, Yves Goergen wrote: > Is my design bad? I should explain why I do it this way at all. There's some other tables in my system that need to keep a list of "keys" (i.e. user IDs) for several actions. A message (one of the tables) has one keylist for read access, one for alter access and a third one for reply access. The lists (if not NULL = empty) contain a list of authorised users to perform the respective action. (If NULL, everybody's allowed.) In an older design, I had a separate table for each list type, which was 6 tables altogether. Instead of a "keylist"."KeylistId", there was e.g. a "MessageReadAccessKeys"."MessageId" referencing "message"."MessageId". First, this makes 5 more tables and second, I doubt that it would solve my "non-unique foreign key" problem. What I need is to store those keys for several tasks, object types and instances of them. What I would like to have is the DBMS keeping those references valid. I guess my last chance is implementing this check in my application (which I already had before I chose to entirely rely on referential integrity and then deleted these few lines). Please tell me if there's a better way. PS: I searched a little more and found out that PostgreSQL also forbids foreign keys referencing non-unique columns (like in SQL92) due to serious bugs in the past, which is one more reason why I don't want to keep it this way. (The whole thing should be somewhat portable...) -- 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]
Foreign keys on non-unique columns (problem)
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]
