Trying to add foreign key in mysql with heidisql

Question

I've been trying to add a foreign key to my table using heidisql and I keep getting the error 1452.

After reading around I made sure all my tables were running on InnoDB as well as checking that they had the same datatype and the only way I can add my key is if I drop all my data which I don't intend to do since I have spent quite a few hours on this.

here is my table create code:

CREATE TABLE `data` (
`ID` INT(10) NOT NULL AUTO_INCREMENT,
#bunch of random other columns stripped out
`Ability_1` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
#more stripped tables
`Extra_Info` SET('1','2','3','Final','Legendary') NOT NULL DEFAULT '1' COLLATE 'utf8_unicode_ci',
PRIMARY KEY (`ID`),
UNIQUE INDEX `ID` (`ID`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=650;

here is table 2

CREATE TABLE `ability` (
`ability_ID` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
#stripped columns
`Name_English` VARCHAR(12) NOT NULL COLLATE 'utf8_unicode_ci',
PRIMARY KEY (`ability_ID`),
UNIQUE INDEX `ability_ID` (`ability_ID`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=165;

Finally here is the create code along with the error.

ALTER TABLE `data`
ADD CONSTRAINT `Ability_1` FOREIGN KEY (`Ability_1`) REFERENCES `ability` (`ability_ID`) ON UPDATE CASCADE ON DELETE CASCADE;
/* SQL Error (1452): Cannot add or update a child row: a foreign key constraint fails (`check`.`#sql-ec0_2`, CONSTRAINT `Ability_1` FOREIGN KEY (`Ability_1`) REFERENCES `ability` (`ability_ID`) ON DELETE CASCADE ON UPDATE CASCADE) */

If there is anything else I can provide please let me know this is really bothering me. I'm also using 5.5.27 - MySQL Community Server (GPL) that came with xampp installer.

Solution 1

If you are using HeidiSQL it is pretty easy.

Just see the image, click on the +Add to add foreign keys.

I prefer GUI way of creating tables and its attribute because it saves time and reduces errors.

Solution 2

Here is how you can do it ;

1. Create your Primary keys. For me this was straight forward so I won't post how to do that here

2. To create your FOREIGN KEYS you need to change the table / engine type for each table from MyIASM to InnoDb. To do this Select the table on the right hand side then select the OPTIONS tab on the right hand side and change the engine from MyIASM to InnoDb for every table.