List:General Discussion« Previous MessageNext Message »
From:PJ Date:May 22 2009 7:17pm
Subject:Re: cannot alter table - solved
View as plain text  
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

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