I'm trying to add a foreign key to a MySQL InnoDB table, linking a
BIGINT with a table within the same database. I'm using MySQL Workbench to do this. It generates a script:
ALTER TABLE `MyDatabase`.`MyTable` ADD CONSTRAINT `myNewForeignKey` FOREIGN KEY (`TheId` ) REFERENCES `MyDatabase`.`MyReferencedTable` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION , ADD INDEX `myNewForeignKey_idx` (`TheId` ASC) ;
When applied I get:
ERROR 1005: Can't create table 'MyDatabase.#sql-1ab6_5c3df97' (errno: 150) ERROR: Error when running failback script. Details follow. ERROR 1050: Table 'MyReferencedTable' already exists
I even tried removing the table (could do that as it's empty) and recreating it with foreign keys and all, but it leads to the same errors.
I've seen reports of bugs relating to this, but they are several years old and should've been fixed by now. Surely foreign keys must be possible to add. What am I doing wrong?