Why does table-create fail when everything's innodb, but not when one table is MyIsam?
Create your tables without the Foreign Key constraints. Although the same statements work with MyISAM engine, the constraints are silently ignored there - that is why you are not getting the errors. If you really need those constraints, then create them correctly.
However, I generally tend to avoid FK constraints and implement the constraints at application level One problem I spot right away are the symbols of your constraints which have to be unique at DB level and you have fk_book_item both on tag_book_item_relation table and on book table.
Create your tables without the Foreign Key constraints. Although the same statements work with MyISAM engine, the constraints are silently ignored there - that is why you are not getting the errors. If you really need those constraints, then create them correctly.
However, I generally tend to avoid FK constraints and implement the constraints at application level. One problem I spot right away are the symbols of your constraints which have to be unique at DB level and you have fk_book_item both on tag_book_item_relation table and on book table.
Thank very much, I figured it out! – xiaohan2012 Jun 29 at 13:14.
It seems there is an issue with the "CREATE TABLE book" that the foreign key constraint fk_book_item has the same name as the constraint in tag_book_item_relation. Try using another name for the constraint in book and the CREATE TABLE should work fine. This isn't a problem in MyISAM because they have no concept of foreign-keys and so the FK constraints are ignored.
Hope this helps!
ERROR 121 says "Table creation failed because a foreign key constraint was not correctly formed. If the error message refers to error –1, table creation probably failed because the table includes a column name that matched the name of an internal InnoDB table. " Link The Index Name and Constraint Name may be same, change that try creating the table.
Thanks for sharing the link! – xiaohan2012 Jun 29 at 13:15.
This error message is saying that there is a duplicate key somewhere. It can be caused by a name conflict in a foreign key constraint; you cannot use the same foreign key name in different tables. (I don't know what other tables might be in your data base.) Often, the error is caused by the table already existing in InnoDB's internal dictionary, even though the .
Frm file is gone. If that's the case, then the easiest thing to do is to do an sql dump of the data, drop the data base, recreate it, and then load the data from the dump.
Say, if I want to create a simple library system. And I have four tables. 2, table book, which represents a specific real object of the book item.
So a book_item object can relate to many book objects. 3, table tag, which represents a book tag. Like science, literature, architecture and so on.
4, table tag_book_item_relation, which relates tags to book_items. So, the relations are as below. 2,book_item to tag is many-to-many relationship.
However, if I change the engine of book or tag_book_item_relation to MyISAM, everything will be fine.
I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.