List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:May 22 2009 7:21pm
Subject:Re: cannot alter table - solved
View as plain text  
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` (
>>>>>  `bookID` smallint(6) unsigned NOT NULL,
>>>>>  `categories_id` int(2) unsigned NOT NULL,
>>>>>  PRIMARY KEY (`bookID`,`categories_id`),
>>>>>  KEY `fk_book_categories_books` (`bookID`),
>>>>>  KEY `fk_book_categories_categories` (`categories_id`),
>>>>>  CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`)
> REFERENCES
>>>>> `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
>>>>> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
>>>>>
>>>>> Anybody out there still up? I'm rather desperate to fix this this
> evening...
>>>>> Thanks in advance.
>>>>>
>>>>>
>>>> We will need a little more information.  The table looks sound but
> is
>>>> clearly designed to link  2 other tables.  If you are failing
> to
>>>> insert or update, it seems likely that it is because the data is
>>>> absent in the foreign tables.  Can you confirm?  Because
> without that
>>>> 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_id
>>> 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.  All relational
>> tables  need 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:
>>       KEY `fk_book_categories_books` (`bookID`),
>> bookID, being the first part of your compound primary key, is
>> effectively indexed already.  The 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é Kempf: "Pour sauver la planĨte, sortez du capitalisme."
> -------------------------------------------------------------
> Phil Jourdan --- pj@stripped
>   http://www.ptahhotep.com
>   http://www.chiccantine.com/andypantry.php
>
>



-- 
 - michael dykman
 - mdykman@stripped

 - All models are wrong.  Some models are useful.
Thread
cannot alter table - rather urgentPJ22 May
  • Re: cannot alter table - rather urgentMichael Dykman22 May
    • Re: cannot alter table - solvedPJ22 May
      • Re: cannot alter table - solvedMichael Dykman22 May
        • Re: cannot alter table - solvedPJ22 May
          • Re: cannot alter table - solvedMichael Dykman22 May
  • Re: cannot alter table - rather urgentPeter Brawley22 May