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;

转载自：https://www.cnblogs.com/programmer-tlh/p/5782451.html