ON DELETE CASCADE not working in MySQL

Question

I use the following SQL to create a table called app_info :

 CREATE TABLE IF NOT EXISTS `app_info` ( `_id` int(11) NOT NULL AUTO_INCREMENT, `app_name` varchar(50) DEFAULT NULL, `app_owner` varchar(50) DEFAULT NULL, `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; 

I use the following SQL to create a table called tab_info :

 CREATE TABLE `myDB`.`tab_info` ( `_id` INT NOT NULL AUTO_INCREMENT , `app_id` INT NOT NULL , `tab_title` VARCHAR(15) NOT NULL , PRIMARY KEY (`_id`) , UNIQUE INDEX `app_id_UNIQUE` (`app_id` ASC) , INDEX `app_tab_key` (`app_id` ASC) , CONSTRAINT `app_tab_key` FOREIGN KEY (`app_id` ) REFERENCES `myDB`.`app_info` (`_id` ) ON DELETE CASCADE ON UPDATE CASCADE); 

But when I delete data from the primary key table, orphan rows in the foreign key table are not deleted automatically. Does anyone know what the problem is?

Answer 1

The MyISAM storage engine does not support foreign key restrictions. The constraint is parsed, but silently ignored.

To fix your problem, use the InnoDB engine instead (for both tables).

 CREATE TABLE ( ... ) ENGINE = InnoDB ... ; 

---

Instead of dropping your tables and re-creating them, you can also change the storage mechanism:

 ALTER TABLE myDB.app_info ENGINE = InnoDB; ALTER TABLE myDB.tab_info ENGINE = InnoDB; 

After changing the engine, you will need to add the foreign key constraint again.