Michael Dykman wrote: > On Fri, May 22, 2009 at 12:26 AM, PJ <af.gour...@videotron.ca> wrote: > >> Michael Dykman wrote: >> >>> On Thu, May 21, 2009 at 11:06 PM, PJ <af.gour...@videotron.ca> 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 --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
