MySQL基础之数据管理【4】

亦凉 2023-06-01 11:13 5阅读 0赞

### 外键约束的使用(只有InnoDB存储引擎支持外键) ###

create table news_cate( id tinyint unsigned auto_increment key comment '编号', cateName varchar(50) not null unique comment '分类名称', cateDesc varchar(100) not null default '' comment '分类描述' )engine=innodb charset=utf8; insert news_cate(cateName) values('国内新闻'), ('国际新闻'), ('娱乐新闻'), ('体育新闻'); create table news( id int unsigned auto_increment key comment '编号', title varchar(100) not null unique comment '新闻标题', content varchar(1000) not null comment '新闻内容', cateId tinyint not null comment '新闻所属分类编号' )engine=innodb charset=utf8; insert news(title,content,cateId) values('a1','aaaa1',1), ('a2','aaaa2',1), ('a3','aaaa3',4), ('a4','aaaa4',2), ('a5','aaaa5',3);

#### 将会产生脏数据的操作 ####

delete from news_cate where id=2; insert news(title,content,cateId) values('a6','aaaa6',45);

#### 解决办法：添加外键(保证数据的一致性和完整性) ####

--建表时指定外键([constraint 外键名称] foreign key(外键字段名称) references 主表(主键字段名称)) --news中cateId的字段类型和约束条件要与主表news_cate中的id相似 --如果外键字段没有创建索引，MySQL会自动帮我们添加索引 --子表的外键关联的必须是父表的主键 create table news( id int unsigned auto_increment key comment '编号', title varchar(100) not null unique comment '新闻标题', content varchar(1000) not null comment '新闻内容', cateId tinyint unsigned not null comment '新闻所属分类编号', constraint cateId_fk_newsCate foreign key(cateId) references news_cate(id) )engine=innodb charset=utf8; insert news(title,content,cateId) values('a1','aaaa1',1), ('a2','aaaa2',1), ('a3','aaaa3',4), ('a4','aaaa4',2), ('a5','aaaa5',3); --父表中的记录有内容时(比如国内新闻里有新闻就不能删除国内新闻这个类别)不能进行以下操作(没有外键约束的参照操作时) --插入非法记录 insert news(title,content,cateId) values('b1','bbbb1',8); --新闻类别里没有8，报错 --显示：有外键约束不能添加非法记录 mysql> insert news(title,content,cateId) values('b1','bbbb1',8); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`king`.`news`, CONSTRAINT `news_ibfk_1` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`)) --删除或更新父表中的记录 delete from news_cate where id=1; --新闻类别1里有记录不能删除，报错 update news_cate set id=10 where id=1; --新闻类别1里有记录不能更新，报错 --显示：有外键约束不能删除或更新父类记录 mysql> delete from news_cate where id=1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`king`.`news`, CONSTRAINT `news_ibfk_1` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`)) --删除子表之后才能删出父表

### 动态创建外键及删除外键操作 ###

--动态添加外键 --动态添加外键之前表中的记录一定是合法的记录，没有脏值，否则外键添加不成功 alter table tbl_name add [constraint 外键名称] foreign key(外键字段名称) references 主表(主键字段名称); --动态删除外键 alter table tbl_name drop foreign key fk_name; --外键约束的参照操作 --cascade:从父表删除或更新，子表也跟着删除或更新，级联的操作 --set null:从父表删除或更新记录，并设置子表的外键列为null(要保证子表外键的字段可以为null) --not action|restrict:拒绝对父表做更新或删除操作 alter table news add foreign key(cateId) references news_cate(id) on delete cascade on update cascade;

转载于:https://www.cnblogs.com/huowuyan/p/11469369.html