请教一个联级操作 数据库中要联级操作怎么代码怎么写的啊?就是两个表中有个字段是关联的,其中一个表中的一行记录删除,另一张表对应的行也要删除,这个语句怎么写的呀?知道的朋友请说一下好吗>就写关键的那一句就行.谢谢 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 以下摘自 MySQL 5.1 Reference Manual13.5.6.4. FOREIGN KEY Constraints[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...) REFERENCES tbl_name (index_col_name, ...) [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}] == 思想重于技巧 == 以下摘自 MySQL 5.1 Reference ManualexampleCREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;CREATE TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) ENGINE=INNODB; == 思想重于技巧 == 这个我知道额,我试了还是不对,可能不是这个地方出现错误DROP TABLE IF EXISTS `blog`.`user`;CREATE TABLE `blog`.`user` ( `user_id` int(10) unsigned zerofill NOT NULL auto_increment, `user_name` varchar(45) NOT NULL, `user_pass` varchar(45) NOT NULL, `account_id` int(10) unsigned zerofill NOT NULL, PRIMARY KEY (`user_id`), KEY `FK_user_1` (`account_id`), CONSTRAINT `FK_user_1` FOREIGN KEY (`account_id`) REFERENCES `account` (`account_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS `blog`.`account`;CREATE TABLE `blog`.`account` ( `account_id` int(10) unsigned zerofill NOT NULL auto_increment, `firstname` varchar(45) NOT NULL, `lastname` varchar(45) NOT NULL, PRIMARY KEY (`account_id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;通过user表中的user_id删除,对应的account也要删除请问该怎么写?谢谢PS,我没有分给你们了.但如果您知道的话请告诉我一声 你的 CREATE TABLE `blog`.`account` ( 的foriegn key定义在哪儿? == 思想重于技巧 == CREATE TABLE `blog`.`account` ( `account_id` int(10) unsigned zerofill NOT NULL auto_increment, `firstname` varchar(45) NOT NULL, `lastname` varchar(45) NOT NULL, PRIMARY KEY (`account_id`), FOREIGNKEY (account_id) REFERENCES user(user_id) ON DELETE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; == 思想重于技巧 == 如何使用Mysql一次性读取大量数据 【MySQL】有个案例,如何创建更好的索引才能获取更好的性能? 请教如何查询 怎么去掉数据库表 某列字段前缀? mysql 支持带参数的游标吗? 从SQL SERVICE中生成的脚本,如何导入到MySQL中,我试了好几次,都失败了啊 数据库导入与导出的问题 求MySql Cluster 7.2基准测试工具 mysql-3.23.56-1.73 RPM安装 怎样限制每个用户的并发连接??? 求教这种情况该如何设计表的字段? 一个联级操作的问题 请教相互对应有序的表设计问题
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;
CREATE TABLE `blog`.`user` (
`user_id` int(10) unsigned zerofill NOT NULL auto_increment,
`user_name` varchar(45) NOT NULL,
`user_pass` varchar(45) NOT NULL,
`account_id` int(10) unsigned zerofill NOT NULL,
PRIMARY KEY (`user_id`),
KEY `FK_user_1` (`account_id`),
CONSTRAINT `FK_user_1` FOREIGN KEY (`account_id`) REFERENCES `account` (`account_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS `blog`.`account`;
CREATE TABLE `blog`.`account` (
`account_id` int(10) unsigned zerofill NOT NULL auto_increment,
`firstname` varchar(45) NOT NULL,
`lastname` varchar(45) NOT NULL,
PRIMARY KEY (`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
通过user表中的user_id删除,对应的account也要删除请问该怎么写?谢谢
PS,我没有分给你们了.但如果您知道的话请告诉我一声
你的 CREATE TABLE `blog`.`account` ( 的foriegn key定义在哪儿?
`account_id` int(10) unsigned zerofill NOT NULL auto_increment,
`firstname` varchar(45) NOT NULL,
`lastname` varchar(45) NOT NULL,
PRIMARY KEY (`account_id`),
FOREIGNKEY (account_id) REFERENCES user(user_id) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;