Why does table-create fail when everything's innodb, but not when one table is MyIsam?

Question

I find innodb quite annoying when I try to design a db structure, at least compared with MyIsam, which seems to have less limitations

Say, if I want to create a simple library system. And I have four tables.

1,table book_item, which records the book_name, author, publish time and those basic information about books

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.

1,we have a book item to book is one-to-many relationship

2,book_item to tag is many-to-many relationship.

Note here, engine for the table are all innodb If I try to create the tables, it will fail:

Error:
Executing SQL script in server
ERROR: Error 1005: Can't create table 'yet_another_test.book' (errno: 121)

However, if I change the engine of book or tag_book_item_relation to MyISAM, everything will be fine.

So, I am wondering what is going wrong if I use engine innodb for tablebook and tag_book_item_relation

The sql script is here(forward engineering in MySQL workbench):

CREATE  TABLE IF NOT EXISTS `yet_another_test`.`tag` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `yet_another_test`.`book_item` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `yet_another_test`.`tag_book_item_relation` (
  `book_item_id` INT NOT NULL ,
  `tag_id` INT NOT NULL ,
  PRIMARY KEY (`book_item_id`, `tag_id`) ,
  INDEX `fk_tag` (`tag_id` ASC) ,
  INDEX `fk_book_item` (`book_item_id` ASC) ,
  CONSTRAINT `fk_tag`
    FOREIGN KEY (`tag_id` )
    REFERENCES `yet_another_test`.`tag` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_book_item`
    FOREIGN KEY (`book_item_id` )
    REFERENCES `yet_another_test`.`book_item` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `yet_another_test`.`book` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `book_item_id` INT NOT NULL ,
  PRIMARY KEY (`id`, `book_item_id`) ,
  INDEX `fk_book_item` (`book_item_id` ASC) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  CONSTRAINT `fk_book_item`
    FOREIGN KEY (`book_item_id` )
    REFERENCES `yet_another_test`.`book_item` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

The primary key is already unique, so there's no need to add a unique index in `tag` and `book_item`. — True Soft, Jun 28 '11 at 03:53
Huh? InnoDB is awesome, MyISAM is the devil. Why a database engine that does not understand the concept of a transaction is even supported, let alone used by default, by MySQL is beyond me. A non-transactional database is a toy, at best. — aroth, Jun 28 '11 at 03:58

score 2 · Answer 1 · answered Jun 28 '11 at 05:53

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!

score 1 · Answer 2 · answered Jun 28 '11 at 03:55

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.

Tudor Constantin · Accepted Answer · 2011-06-28T04:01:45.933

1

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

edited Jun 28 '11 at 04:01

answered Jun 28 '11 at 03:55

Tudor Constantin

26,330
7
49
72

1

Suggesting not to use FK is questionable at best http://stackoverflow.com/questions/15616578/foreign-keys-when-cascades-arent-needed – o0'. Mar 30 '13 at 23:26
I'm a little paranoid - I like to do everything explicitly - when I delete an `orders` record, I don't want my `order-lines` to be deleted, unless I delete them explicitly. Yes, I might get orphan records in DB, but usually that is what I want. I think it's a matter of preferences here. – Tudor Constantin Mar 31 '13 at 05:46

score 1 · Answer 4 · answered Jun 28 '11 at 04:03

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.

Why does table-create fail when everything's innodb, but not when one table is MyIsam?

4 Answers4