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?
MyTable(TheId)andMyReferencedTable(id)bothbigint unsigned(or bothbigint signed)? – ypercube May 27 at 14:03BIGINT(20)and they are unsigned. – Gruber May 27 at 14:05MyReferencedTable(id)have aPRIMARY KEYorUNIQUEconstraint? – ypercube May 27 at 14:06MyReferencedTableis the autoincrementidcolumn (BIGINT(20)). It also has two indices, but noUNIQUEconstraints. – Gruber May 27 at 14:09