On Dec 7, 2009, at 5:20 AM, Paul Shaffer wrote:
> Thanks. Typo is only in my post edit, not in the actual software.
> Your edit
> is correct. Question still stands.
The "parent key" of a foreign key relationship must be the primary
key of its table, or else have a UNIQUE constraint on it. The parent
key of:
REFERENCES [P_item]([ColumnID],[ItemID])
meet this criteria because (ItemID, ColumnID) is the primary key of
"P_item". Neither of the parent keys of the following are unique or
primary keys, so they produce an error.
FOREIGN KEY([ColumnID])
REFERENCES [P_item] ([ColumnID]) ON UPDATE CASCADE ON DELETE
FOREIGN KEY([ParentID])
REFERENCES [P_item] ([ItemID]) ON UPDATE CASCADE ON DELETE
CASCADE,
Dan.
>> On Sun, 06 Dec 2009 00:05:28 -0700, Paul Shaffer
>> <[email protected]> wrote:
>>
>>> I found where I was getting a 'foreign key mismatch' error, but I
>>> don't
>>> know why. There are 2 versions of the last table below. They look
>>> like they
>>> are about the same to me, but one causes the error. The error
>>> occurs when I
>>> am doing a cascading delete in unrelated tables, and the problem
>>> table has
>>> no rows. It's important for me since it impacts the code for a
>>> tool I am
>>> writing. Thanks in advance for any help.
>>
>> Your code is not valid.
>> This may not be the cause of the error, but anyway.
>>
>>> -- causes foreign key mismatch error:
>>>
>>> CREATE TABLE [P_Item_2] (
>>> [ItemID] integer NOT NULL,
>>> [ColumnID] integer NOT NULL,
>>> [ParentID] integer NOT NULL,
>>> PRIMARY KEY ([ItemID], [ColumnID]),
>>> FOREIGN KEY([ColumnID])
>>> REFERENCES [P_item] ([ColumnID]) ON UPDATE CASCADE ON DELETE
>>> FOREIGN KEY([ParentID])
>>> REFERENCES [P_item] ([ItemID]) ON UPDATE CASCADE ON DELETE
>>> CASCADE,
>>> CASCADE
>>> )
>>
>>
>> Shouldn't that be:
>>
>> CREATE TABLE [P_Item_2] (
>> [ItemID] integer NOT NULL,
>> [ColumnID] integer NOT NULL,
>> [ParentID] integer NOT NULL,
>> PRIMARY KEY ([ItemID], [ColumnID]),
>> FOREIGN KEY([ColumnID])
>> REFERENCES [P_item] ([ColumnID])
>> ON UPDATE CASCADE
>> ON DELETE CASCADE
>> FOREIGN KEY([ParentID])
>> REFERENCES [P_item] ([ItemID])
>> ON UPDATE CASCADE
>> ON DELETE CASCADE
>> );
>>
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
