'Error 1061 (MYSQL Workbench) duplicate keys when creating indexes

I cannot understand why I keep getting an 1061 error for key duplicates on indexes when I dont have any duplicates. I've looked at other stackoverflow posts regarding similar questions, and most had this issue when they had the same name for multiple foreign keys. However, that doesn't apply in my case.

In a general nutshell what I've done is create a table linking books to tasks. Each book id will have a few tasks associated with that particular book. It is a many many relationship i.e. many books will be associated with a specific task. And a specific task will be associated with many books.

enter image description here

So in between this many to many relationship I have a Books x Tasks table which acts as the intermediary. This table has its own primary key and two foreign keys. One is the book ID from the books table and the tasks id from the tasks table. Therefore they are both foreign keys. In that way I can link multiple tasks with multiple books each defined by it's own unique id within the booksxtasks table.

When I forward engineer this, there is an error.

Executing SQL script in server ERROR: Error 1061: Duplicate key name 'FK Tasks_TaskID_idx' SQL Code: CREATE INDEX `FK Tasks_TaskID_idx` ON `mydb`.`BooksxTasks` (`Tasks_TaskID` ASC)

  CREATE TABLE IF NOT EXISTS `mydb`.`BooksxTasks` (
  `BooksxTasksID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `Books_BookID` INT UNSIGNED NOT NULL,
  `Tasks_TaskID` TINYINT(2) UNSIGNED NOT NULL,
  PRIMARY KEY (`BooksxTasksID`),
  CONSTRAINT `FK Tasks_TaskID`
    FOREIGN KEY (`Tasks_TaskID`)
    REFERENCES `mydb`.`Tasks` (`TaskID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FK Books_BooksID`
    FOREIGN KEY (`Books_BookID`)
    REFERENCES `mydb`.`Books` (`BookID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `FK Tasks_TaskID_idx` ON `mydb`.`BooksxTasks` (`Tasks_TaskID` ASC);

CREATE INDEX `FK Books_BooksID_idx` ON `mydb`.`BooksxTasks` (`Books_BookID` ASC);

I also checked to see all the names given to all the constraints in the entire database. There was no duplicate names. So where is this duplicate key error coming from?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source