Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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` )
, 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?

share|improve this question
Show the SQL that Workbench produces. – ypercubeᵀᴹ May 27 '13 at 13:54
Are MyTable(TheId) and MyReferencedTable(id) both bigint unsigned (or both bigint signed)? – ypercubeᵀᴹ May 27 '13 at 14:03
@ypercube: Both are BIGINT(20) and they are unsigned. – Gruber May 27 '13 at 14:05
Does MyReferencedTable(id) have a PRIMARY KEY or UNIQUE constraint? – ypercubeᵀᴹ May 27 '13 at 14:06
@ypercube: Yes, primary key in MyReferencedTable is the autoincrement id column (BIGINT(20)). It also has two indices, but no UNIQUE constraints. – Gruber May 27 '13 at 14:09

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.

share|improve this answer

If anyone encounters this problem in phpmyadmin, it could be due to the fact you have multiple identical constraints on a field. That can happen sometimes.

Check with SHOW CREATE TABLE to obtain all constrains. In case you find duplicates, erase one of them.

Then, in phpMyAdmin, you will probably need to setup all constraints from beginning for that table.So, erase and re-create all FKs through phpmyadmin for that table.

And then, it should work.

share|improve this answer
This question already has an answer - and phpmyadmin has nothing to do with it. Nor multiple constraints. The referenced table was not InnoDB. – ypercubeᵀᴹ Feb 9 '15 at 2:57

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.