MySQL进阶之约束

MySQL进阶

1. 外键约束

   • 外键约束概念

     • 让表和表之间产生关系，从而保证数据的准确性

   • 建表时添加外键约束

   CREATE TABLE 表名(
   		列名  数据类型约束,
   		CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
   );
   

   • 删除外键约束

     -- 标准语法
     ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
     

   • 建表后添加外键约束

     -- 标准语法
     ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);
     

   • 例子

     create database if not exists db2;
     use db2;
     -- 创建用户表
     create table if not exists user(
     	id int primary key auto_increment,  -- id
     	name varchar(20) not null  -- 姓名
     );
     -- 添加用户数据
     INSERT into user values (null, "张三"),(null, "李四") ,(NULL,"王五");
     
     -- 创建orderlist订单表
     create table if not exists orderlist(
     	id int primary key auto_increment, -- id
     	number varchar(20) not null,  -- 订单编号
     	uid int,  -- 外键列
     	constraint ou_kl foreign key (uid) references user(id)  -- 添加外键约束
     );
     
     -- 添加订单数据
     insert into orderlist values (NULL,'hm001',1),(NULL,'hm002',1),
     (NULL,'hm003',2),(NULL,'hm004',2),
     (NULL,'hm005',3),(NULL,'hm006',3);
     
     -- 添加一个订饭 但是没有所属用户，添加不了
     isert into orderlist values (null, "hm007", 8);
     
     -- 删除王五这个用户，但是订单中还有王五多个订单，无法删除
     delete from user where name="王五";
     
     -- 删除外键约束
     alter table orderlist drop foreign key ou_kl;
     
     
     -- 建表后添加外键约束
     alter table orderlist add constraint ou_fk foreign key (uid) references user(id);
     

2. 外键的级联更新和级联删除

   • 外键的级联更新和级联删除

     • 级联更新：当主表中的数据进行修改时，从表中相关的数据也随之更新
     • 级联删除：当主表中的数据进行删除时，从表中相关的数据也随之删除

   • 例子

     -- 添加外键约束，同时添加级联更新  标准语法
     ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE;
     
     -- 添加外键约束，同时添加级联删除  标准语法
     ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON DELETE CASCADE;
     
     -- 添加外键约束，同时添加级联更新和级联删除  标准语法
     ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) ON UPDATE CASCADE ON DELETE CASCADE;
     
     
     -- 删除外键约束
     ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;
     
     -- 添加外键约束，同时添加级联更新和级联删除
     ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) ON UPDATE CASCADE ON DELETE CASCADE;
     
     -- 将王五用户的id修改为5    订单表中的uid也随之被修改
     UPDATE USER SET id=5 WHERE id=3;
     
     -- 将王五用户删除     订单表中该用户所有订单也随之删除
     DELETE FROM USER WHERE id=5;
     
     -- 删除外键约束
     alter table orderlist drop foreign key ou_fk;
     
     -- 添加外键约束，同时添加级联更新和级联删除
     alter table orderlist add constraint ou_fkl foreign key (uid) references user(id) on update cascade on delete cascade;
     
     -- 李四用户id改为5，订单表中的uid也自动改为5
     update user set id=5 where name='李四' ;
     
     -- 将李四这个用户删除,订单表uid和李四id相对应的数据也删除
     delete from user where name="李四";