From: Michael Dykman
Date: May 22 2009 7:21pm
Subject: Re: cannot alter table - solved
List-Archive: http://lists.mysql.com/mysql/217657
Message-Id: <814b9a820905221221t4d03dd76g94df6c9aeb18f3ad@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-2
Content-Transfer-Encoding: quoted-printable

You do need the foreign keys for integrity and the columns which make
the foreign reference should be indexed as well.  My only point is
that bookID is already indexed as the first element in the primary
key, so the additional index on bookID alone is superfluous.

 - michael


On Fri, May 22, 2009 at 3:17 PM, PJ <af.gourmet@stripped> wrote:
> Michael Dykman wrote:
>> On Fri, May 22, 2009 at 12:26 AM, PJ <af.gourmet@stripped> wrote:
>>
>>> Michael Dykman wrote:
>>>
>>>> On Thu, May 21, 2009 at 11:06 PM, PJ <af.gourmet@stripped> wrote:
>>>>
>>>>
>>>>> I have a seemingly impossible situation. I cannot insert values into =
the
>>>>> tables and I cannot alter or delete the primary key (which should not
>>>>> exist) or delete the foreign keys nor remove the constraint. G search
>>>>> doesn't help.
>>>>>
>>>>> CREATE TABLE `book_categories` (
>>>>> =A0`bookID` smallint(6) unsigned NOT NULL,
>>>>> =A0`categories_id` int(2) unsigned NOT NULL,
>>>>> =A0PRIMARY KEY (`bookID`,`categories_id`),
>>>>> =A0KEY `fk_book_categories_books` (`bookID`),
>>>>> =A0KEY `fk_book_categories_categories` (`categories_id`),
>>>>> =A0CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERE=
NCES
>>>>> `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
>>>>> ) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1
>>>>>
>>>>> Anybody out there still up? I'm rather desperate to fix this this eve=
ning...
>>>>> Thanks in advance.
>>>>>
>>>>>
>>>> We will need a little more information. =A0The table looks sound but i=
s
>>>> clearly designed to link =A02 other tables. =A0If you are failing to
>>>> insert or update, it seems likely that it is because the data is
>>>> absent in the foreign tables. =A0Can you confirm? =A0Because without t=
hat
>>>> forgeign data, these rows are pretty meaningless.
>>>>
>>>> What is it you are trying to do?
>>>>
>>>>
>>>>
>>> I was trying to insert some records to fill up empty id numbers and in
>>> the process noticed that there is a primary key in the tables but
>>> unnecessary if I am not mistaken. Also the book_categories.categories_i=
d
>>> should be referencing categories.id -- I think I had somehow wet up the
>>> table erroneously.
>>> The problem was that one of the books was not entered as it should have
>>> and I was assuming it had been entered (2 others were at the same time =
-
>>> using phpMyAdmin instead of my insert page).
>>> It now works with minimal bugs on the back-end, but the panic is over.
>>> I'll try to fix the primary key issue next.
>>> Thanks for the quick response.
>>>
>>
>> I would suggest that the primary key is imoprtant. =A0All relational
>> tables =A0need a primary key and, in this particular case, the primary
>> key is what is preventing you from creating duplicate rows.
>>
>> If anything needs to go:
>> =A0 =A0 =A0 KEY `fk_book_categories_books` (`bookID`),
>> bookID, being the first part of your compound primary key, is
>> effectively indexed already. =A0The key listed above is quite
>> unnecessary.
>>
>>
> Actually, that key (book_categories.bookID) references book.id;
> book_categories.categories_id references categories.id. Is'nt it
> necessary for both to have foreigh keys? Things so far are working fine..=
.
>
> --
> Herv=E9 Kempf: "Pour sauver la plan=E8te, sortez du capitalisme."
> -------------------------------------------------------------
> Phil Jourdan --- pj@stripped
> =A0 http://www.ptahhotep.com
> =A0 http://www.chiccantine.com/andypantry.php
>
>



--=20
 - michael dykman
 - mdykman@stripped

 - All models are wrong.  Some models are useful.