 | | | Subject: Re: Foreign keys on non-unique columns (problem) | Subject: Re: Foreign keys on non-unique columns (problem) 2007-11-04 - By Yves Goergen
Back
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" <nospam.list@(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/mysql?unsub=mysql@(protected)
|
|
|