MySQL InnoDB CASCADE？ [英] MySQL InnoDB CASCADE?

问题描述

我开始尝试在Web应用程序中使用InnoDB。我已经用外键安装了一些表，但是它们不像预期的那样工作。
这里是我的表CREATE语句：

pre $ CREATE TABLE sections（
section_id INT NOT NULL AUTO_INCREMENT，
title VARCHAR（30），
created_at int（10）NOT NULL，
updated_at int（10）NOT NULL，

PRIMARY KEY（section_id）
）ENGINE = InnoDB;

CREATE TABLE页面（
page_id INT NOT NULL AUTO_INCREMENT，
section_idfk INT NOT NULL，

PRIMARY KEY（page_id），
FOREIGN KEY（section_idfk）REFERENCES节（section_id）
ON DELETE CASCADE
ON UPDATE CASCADE
）ENGINE = InnoDB;

表格创建成功，我可以用数据填充它们，但是，我期待着我所做的任何更改到Sections表中以对Pages表中的相应行产生影响。我试着改变一个部分的ID，并且完全删除一个部分。在这两种情况下，Pages表都不受影响。

任何人都可以看到我要去哪里吗？任何意见赞赏。

谢谢。

解决方案

我很快把两个类似的表在MySQL查询浏览器用下面的定义：

$ pre $ $ code> DROP TABLE如果存在`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如果存在`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`）参考`sections`（`section_id`）ON DELETE CASCADE ON UPDATE CASCADE
）ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8;

与您发布的内容不完全相同，但足够接近。

我插入一行的部分。
我将一个匹配section_id的行添加到pages表中。
然后我做一个DELETE FROM部分;它也从页面中删除。

工作得很好。

编辑 - 我输入了您的创建和它工作也很好。

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, 
    title       VARCHAR(30), 
    created_at  int(10) NOT NULL, 
    updated_at  int(10) NOT NULL,

    PRIMARY KEY(section_id)
) ENGINE=InnoDB; 

CREATE TABLE pages ( 
    page_id       INT NOT NULL AUTO_INCREMENT, 
    section_idfk  INT NOT NULL, 

    PRIMARY KEY(page_id), 
    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.

解决方案

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.

这篇关于MySQL InnoDB CASCADE？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！