MySQL物理外键和逻辑外键 mysql外键是什么

一，什么是外键约束

外键约束（FOREIGN KEY，缩写FK）是用来实现数据库表的参照完整性的。外键约束可以使两张表紧密的结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性。

外键是指表中某个字段的值依赖于另一张表中某个字段的值，而被依赖的字段必须具有主键约束或者唯一约束。被依赖的表我们通常称之为父表或者主表，设置外键约束的表称为子表或者从表。

举个例子：如果想要表示学生和班级的关系，首先要有学生表和班级表两张表，然后学生表中有个字段为stu_clazz（该字段表示学生所在的班级），而该字段的取值范围由班级表中的主键cla_no字段（该字段表示班级编号）的取值决定。那么班级表为主表，学生表为从表，且stu_clazz字段是学生表的外键。通过stu_clazz字段就建立了学生表和班级表的关系。

主表（父表）：班级表  -  班级编号 - 主键

从表（子表）：学生表 - 班级编号 - 外键

 sql语句

1. -- 先创建父表：班级表：
2. create table t_class(
3.         cno int(4) primary key auto_increment,
4.         cname varchar(10) not null,
5.         room char(4)
6. )
7. 
8. -- 添加班级数据：
9. insert into t_class values (null,'java001','r803');
10. insert into t_class values (null,'java002','r416');
11. insert into t_class values (null,'大数据001','r103');
12. 
13. -- 可以一次性添加多条记录：
14. insert into t_class values (null,'java001','r803'),(null,'java002','r416'),(null,'大数据001','r103');
15. 
16. -- 查询班级表：
17. select * from t_class;
18. -- 学生表删除：
19. drop table t_student;
20. -- 创建子表,学生表：
21. create table t_student(
22.         sno int(6) primary key auto_increment,
23.         sname varchar(5) not null,
24.         classno int(4)  -- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
25. );
26. 
27. -- 添加学生信息：
28. insert into t_student values (null,'张三',1),(null,'李四',1),(null,'王五',2);
29. 
30. -- 查看学生表：
31. select * from t_student;
32. 
33. 
34. 
35. -- 出现问题：
36. -- 1.添加一个学生对应的班级编码为4：
37. insert into t_student values (null,'丽丽',4);
38. -- 2.删除班级2：
39. delete from t_class where cno = 2;
40. 
41. -- 出现问题的原因：
42. -- 因为你现在的外键约束，没用语法添加进去，现在只是逻辑上认为班级编号是外键，没有从语法上定义
43. 
44. 
45. -- 解决办法，添加外键约束：
46. -- 注意：外键约束只有表级约束，没有列级约束：
47. create table t_student(
48.         sno int(6) primary key auto_increment,
49.         sname varchar(5) not null,
50.         classno int(4),-- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
51.                                 constraint fk_stu_classno foreign key (classno) references t_class (cno)
52. );
53. 
54. create table t_student(
55.         sno int(6) primary key auto_increment,
56.         sname varchar(5) not null,
57.         classno int(4)
58. );
59. -- 在创建表以后添加外键约束：
60. alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno)
61. -- 上面的两个问题都解决了：
62. -- 添加学生信息：
63. -- > 1452 - Cannot add or update a child row: a foreign key constraint fails (`mytestdb`.`t_student`, CONSTRAINT `fk_stu_classno` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
64. insert into t_student values (null,'张三',1),(null,'李四',1),(null,'王五',2);
65. -- 删除班级1：
66. -- 2.删除班级2：
67. insert into t_student values (null,'张三',3),(null,'李四',3),(null,'王五',3);
68. -- > 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`mytestdb`.`t_student`, CONSTRAINT `fk_stu_classno` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
69. delete from t_class where cno = 3;

二 ，外键策略

1.no action  不允许操作

2.cascade 级联操作：操作主表的时候影响从表的外键信息：

3,set null  置空操作

sql语句

-- 两个表
-- 学生表和班级表
 -- 创建班级表
 create table t_class(
     cno int(4) primary key auto_increment,
     cname varchar(16) not null,
     cclass varchar(15)
 )-- 删除班级表
 drop table t_class;
 -- 查询班级表
 select * from t_class;-- 添加数据
 insert into t_class values(null,'大数据','2002B'),(null,'大数据','2003B'),(null,'计算机科学与技术','2002B'); 
 insert into t_class values(null,'软件工程','2007B'); -- -- -- -- -- -- -- -- -- -- 
  
 -- 创建学生表
 -- 添加外键约束  
 create table t_student(
     sno int(4) primary key auto_increment,
     sname varchar(15) not null,
     classno int(4),
     constraint fk_atu_classno foreign key (classno) references t_class (cno)
     
 ) -- 在表外添加的sql语句 
 alter table t_student add constraint fk_atu_classno foreign key t_class (cno);
 -- 删除学生表
 drop table t_student;
 -- 查询学生表
 select * from t_student;-- 添加数据
 insert into t_student values(null,'硕硕一号',1),(null,'硕硕二号',2),(null,'硕硕三号',2),(null,'硕硕四号',3),(null,'硕硕五号',4),(null,'硕硕六号',4);
  
 -- 外键策略
 -- 一：no action 不允许操作
 -- 和前面不添加任何东西一样 
  
 -- 二：cascade 级联操作：操作主表的时候影响从表的外键信息：
 -- 先删除之前的外键约束：
 alter table t_student drop foreign key fk_atu_classno;-- 重新添加外键约束：
 alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete cascade;-- 试试更新：
 update t_class set cno = 5 where cno = 3;
 -- 试试删除：
 delete from t_class where cno = 5;-- 策略3：set null  置空操作：
 -- 先删除之前的外键约束：
 alter table t_student drop foreign key fk_stu_classno;
 -- 重新添加外键约束：
 alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update set null on delete set null;-- 试试更新：
 update t_class set cno = 8 where cno = 1;  ##运行后会发现没有更新t_student里面得classno变为null
 -- 注意：
 -- 1. 策略2 级联操作  和  策略2 的  删除操作  可以混着使用：alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete set null ;