开发者

Foreign Keys won't cascade in MySQL

I can't seem to get my cascade action going here. I've got two tables defined as the following.

Can anyone tell me what's wrong? An update to system_rigs "id" won't c开发者_C百科ascade to the messages table.

Messages Table

CREATE TABLE `edr_messages` (  
  `message_id` int(10) unsigned NOT NULL AUTO_INCREMENT,  
  `rig_id` int(10) unsigned NOT NULL,  
  `message` text,  
  PRIMARY KEY (`message_id`,`rig_id`),  
  KEY `pkey` (`message_id`),  
  KEY `rig_id` (`rig_id`),  
  CONSTRAINT `edr_messages_ibfk_1` FOREIGN KEY (`rig_id`)
      REFERENCES `system_rigs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE  
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1$$ 

Rigs Table

CREATE TABLE `system_rigs` (  
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,  
  `name` varchar(45) NOT NULL,  
  PRIMARY KEY (`id`)  
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1$$  


I just tried this out, and it worked fine for me (mysql 5.1.41). Maybe we're not clear on what should happen, so let me show you what I tried... I ran your create table clauses (in reverse order). Then I ran the following test:

insert into system_rigs (name) values('computron');

system_rigs
id: 13
name: computron

insert into edr_messages (rig_id, message) values (13, 'here\'s a message');

edr_messages
id: 2
rig_id: 13
message: here's a message

update system_rigs set id = 26 where id = 13;

system_rigs
id: 26
name: computron

edr_messages
id: 2
rig_id: 26
message: here's a message

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜