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.
--
- 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
