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.gour...@videotron.ca> wrote: > 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 > > -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
