MySQL cannot create foreign key, errors 1005, 150

foreign keyMySQLreferential-integrity

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?

Best Answer

I confused my databases and incorrectly thought I was working with InnoDB databases. Turns out one of them was MyISAM and then foreign keys don't work. Unfortunately, MySQL Workbench doesn't inform the user of this on Windows, but it does on the Macintosh version. (I guess it's good after all to have developers on your team with Macs.)

Hope this helps somebody with the same error message.

Related Question