mysql增加外键约束
2个表一个大哥和小弟
CREATE TABLE `dage` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(32) default '',
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `xiaodi` (
`id` int(11) NOT NULL auto_increment,
`dage_id` int(11) default NULL,
`name` varchar(32) default '',
PRIMARY KEY (`id`),
KEY `dage_id` (`dage_id`),
CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=latin1;
或则是：
新增外键约束：ALTER TABLE `xiaodi` ADD CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`);
删除外键：alter table xiaodi drop foreign key xiaodi_ibfk_1;
----
大哥和小弟插入ok
insert into dage(id,name) values(1,'dage');
insert into xiaodi(dage_id,name) values(1,'xiaodi');
1、把大哥删除（报错）
delete from dage where id=1;
报错内容：14:06:25 delete from dage where id=1 Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`activity_test`.`xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)) 0.062 sec
2、插入新小弟，大哥不存在，报错
insert into xiaodi(dage_id,name) values(2,'xiaodi')
报错内容：
insert into xiaodi(dage_id,name) values(2,'xiaodi') Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`activity_test`.`xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)) 0.062 sec
3、把外键约束增加事件触发限制
先移除外键约束：alter table xiaodi drop foreign key xiaodi_ibfk_1;
再添加级联（delete cascade），删除大哥会把下面的小弟都删除掉
alter table xiaodi add foreign key(dage_id) references dage(id) on delete cascade on update cascade;
删除大哥同时，其下的小弟们被级联删除了。
delete from dage where id=1;
select * from dage where id = 1;
select * from xiaodi where dage_id=1;
