Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

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

1 Answer 1

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

Your Answer

 
discard

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.