MySQL InnoDB CASCADE?

Question

Hi, I am starting to experiment with using InnoDB in web applications. I've setup some tables with a foreign key, but they are not behaving as expected. Here are my table CREATE statements:

CREATE TABLE sections ( 
section_id INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(section_id), 
title VARCHAR(30), 
created_at int(10) NOT NULL, 
updated_at int(10) NOT NULL) 
ENGINE=InnoDB; 

CREATE TABLE pages ( 
page_id INT NOT NULL AUTO_INCREMENT, 
PRIMARY KEY(page_id), 
section_idfk INT NOT NULL, 
FOREIGN KEY(section_idfk) REFERENCES sections(section_id) 
ON DELETE CASCADE 
ON UPDATE CASCADE) 
ENGINE=InnoDB;

The tables create ok and I can populate them with data, however, I was expecting any changes I made to the Sections table to have an effect on the corresponding rows in the Pages table. I tried changing the ID of a section and also deleting a section entirely. In both cases, the Pages table was unaffected.

Can anyone see where I'm going wrong?

Any advice appreciated.
Thanks.

Answer 1

I quickly put together two similar tables in the MySQL Query Browser with the following definitions:

DROP TABLE IF EXISTS `test`.`sections`;
CREATE TABLE  `test`.`sections` (
  `section_id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(30) NOT NULL,
  `created_at` int(10) unsigned NOT NULL,
  `updated_at` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`section_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `test`.`pages`;
CREATE TABLE  `test`.`pages` (
  `page_id` int(10) unsigned NOT NULL auto_increment,
  `section_idfk` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`page_id`),
  KEY `section_idfk` (`section_idfk`),
  CONSTRAINT `section_idfk` FOREIGN KEY (`section_idfk`) REFERENCES `sections` (`section_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

Not exactly the same as the ones you posted, but close enough.

I insert into sections a row. I add a row with a matching section_id into the pages table. Then I do a DELETE FROM sections; and it deletes from pages as well.

Works just fine.

edit - I entered your creates and it works fine too.