From: Michael Dykman Date: May 22 2009 1:25pm Subject: Re: cannot alter table - solved List-Archive: http://lists.mysql.com/mysql/217646 Message-Id: <814b9a820905220625y10ff6c3fp28fe882818078769@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit On Fri, May 22, 2009 at 12:26 AM, PJ wrote: > Michael Dykman wrote: >> On Thu, May 21, 2009 at 11:06 PM, PJ 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. -- - michael dykman - mdykman@stripped - All models are wrong. Some models are useful.