MySQL级联删除和级联修改

1、新建主键table

create table demo1_zhujian (
id int primary key auto_increment,
name varchar(10));

2、新建外键table

create table demo2_waijian (
id int primary key auto_increment,
_id int not null default 0,
name varchar(10),
index (_id),
FOREIGN KEY (_id) REFERENCES demo1_zhujian(id) ON DELETE CASCADE ON UPDATE CASCADE);

create table demo3_waijian (
id int primary key auto_increment,
_id int not null default 0,
name varchar(10),
index (_id),
FOREIGN KEY (_id) REFERENCES demo1_zhujian(id) ON DELETE CASCADE ON UPDATE CASCADE);

3、添加数据

insert into demo1_zhujian 
(name)
values 
('11111');

insert into demo2_waijian
(_id, name)
values
(1, 'demo2_1');

insert into demo3_waijian
(_id, name)
values
(1, 'demo3_1');

4、查询数据

select * from demo1_zhujian;
select * from demo2_waijian;
select * from demo3_waijian;

5、修改主键数据

update demo1_zhujian set id=22 where id=1;

6、重复第4步

 

外键table中修改外键不行

7、主键table中删除数据

delete from demo1_zhujian where id=22;

8、重复第4步

---