外键

外键:foreign key，外面的键(键不在自己表中),如果一张表中有一个字段(非主键)指向另外一张表中的主键，那么该字段称之为外键.

增加外键

1.外键可以在创建表的时候或者创建表之后增加(但是要考虑数据的问题) 一张表可以有多个外键
2.创建表的时候增加外键：在所有的表字段之后，使用foreign key(外键字段)references 外部表(主键字段)

外键要求字段本身必须是一个索引(普通索引)，如果字段本身没有索引，外键会先创建一个索引，然后才会创建外键本身

在新增表之后增加外键：修改表结构
alter table 表名 add [constraint 外键名字(自己命名)] foreign key(外键字段) references 父表(主键字段)；

修改外键&删除外键

外键 不可修改：只能先删除后新增
删除外键语法
Alter table 表名 drop foreign key 外键名； --一张表中可以有多个外键，但是名字不能相同

外键删除不能通过查看表结构体现，应该通过查看表创建语句查看

外键作用

外键默认的作用有两点：一个对父表，一个对子表(外键字段所在的表)

对子表约束：子表数据进行写操作的时候，如果对应的外键字段在父表找不到对应的 匹配，那么操作失败.(约束子表数据操作)

不能新增或者更新子表中的行 外键失败：c_id引用了my_class表中的ID:而4在my_class中不存在

对父表约束：父表数据进行写操作(删和改:都必须涉及到主键本身),如果对应的主键在子表中已经被数据所引用，那么就不允许操作.

外键条件

1.外键要存在：首先必须保证表的存储引擎是innodb(默认的存储引擎)：如果不是innodb存储引擎，那么外键可以创建成功，但是没有约束效果
2.外键字段的字段类型(列类型)必须与父表的主键类型完全一致
3.一张表中外键的名字不能重复
4.增加外键的字段(数据已经存在)，必须保证数据与父表主键要求对应

外键约束

所谓外键约束：就是指外键的作用
之前所讲的外键作用：是默认的作用，其实可以通过对外键的需求，进行定制操作
外键约束有三种约束模式：都是针对父表的约束
District:严格模式(默认的)，父表不能删除或者更新一个已经被子表数据引用的记录
Cascade:级联模式：父表的操作，对应子表关联的数据也跟着操作
Set null:置空模式：父表的操作之后，子表对应的数据(外键字段)被置空

通常的一个合理的做法(约束模式)：删除的时候字表置空，更新的时候子表级联操作
指定模式的语法
foreign key(外键字段) references 父表(主键字段) on delete set null update cascade;


级联更新

删除操作 ：置空

删除置空的前提条件：外键字段允许为空(如果不满足条件，外键无法创建)
可控性：通常在实际开发中，很少使用外键来处理

1. 关系数据库外键概念理论（大学时学习的概念）

教材是：王珊，萨师煊老师的数据库系统概论——第五版，这里直接丢截图了。

• 有三类完整性约束，分别是：实体完整性、参照完整性以及用户定义的完整性。
• 其中实体完整性和参照完整性是关系模型必须满足的完整性约束条件，被称作是关系的两个不变性，应该由关系系统自动支持（例如MySQL要负责这部分约束的检查）
• 用户定义的完整性是应用领域需要遵循的约束条件，体现了具体领域中的语义约束
  • 不做强制检查，最多让人感觉不符合现实或者有点蠢，和程序员不怎么相关😛😛😛
  • 比如：成绩只能是0-100，年龄只能是0-150以内，生日只能是1.1-12.31，类似这种语义约束。

1.1 实体完整性

主要用来描述，数据库中每行数据都应该是可区分的（有某个属性是唯一的！）

1.2 ✅ 参照完整性

• 参照完整性用来描述实体和实体间的联系（可以认为就是图数据库中的关系/边，relationship）
• 可以参考：图数据库初探——4. mysql数据导入neo4j（desktop界面导入）中💫 2.3 关系数据库转为图数据库的规则部分。

• 可以看到，数据库理论中
• 假如F属性是一个表R的属性，但是不是主码;属性K是表S的主码。（表R和表S不一定是不同的表）。如果F和K对应，则称F是表R的外码，并称表R是参照关系（参照表）,表S是被参照关系。
• 可以去看看1.5 MySQL数据库操作示例

• 这里的参照完整性规则，意思就是：如果选课表中的课程id，参照了课程表中的课程id，
• 那么选课表中的课程id的取值，要么为空，要么就等于 课程表中的课程id中某个值。

也是：Mysql错误1452 - Cannot add or update a child row: a foreign key constraint fails怎么办？中这个回答的理论依据。

1.3 用户定义完整性

1.4 ✅ 参照完整性的SQL实现

还是王珊，萨师煊老师的教材，是后面的例子，截图贴上来

主要还是关于参照完整性（外键）这部分的内容

可以看到，对于学生版Student(Sno)、课程表Course(Cno)和学生选课表SC(Sno,Cno)来说，按照上面的规定，

则，Sno是表SC的外码，SC表是参照关系，被参照表是Student；Cno也是表SC外码，SC表是参照关系，被参照表是Course。

1.5 ✅ MySQL数据库操作示例

如果是用Navicat设计mysql数据库，那么可视化应该是这样：

例如，有两个表，分别是poem（诗表）和poet_to_poem(诗人和诗对应表)

poem（诗表）数据

poet_to_poem(诗人和诗对应表)数据

poem（诗表）非空和主键约束
poet_to_poem(诗人和诗对应表)非空和主键约束

很明显，poet_to_poem表中的poem_id是引用自poem表，所以可以设置一个外键（对poet_to_poem表设置）。

所以外键是设置在引用了其他表字段的表上！

设置完之后，ER图显示就是这样，主键是钥匙，外键是个锁扣的形状

1.6 ✅ neo4j中的关系和mysql的外键

之前在：图数据库初探——4. mysql数据导入neo4j（desktop界面导入）中，由于外键设置错误，所以导入的时候，只导入了节点，没有识别出关系。设置正确后，就可以正确识别关系了，如下：

C:\shaiic_work\neo4j_data\relate-data\dbmss\dbms-54e8721d-3337-412a-8b54-d6ccd03b564e\bin\neo4j-admin.bat @C:\Users\HUANGS~1\AppData\Local\Temp\csv-001\neo4j-admin-import-params
很奇怪，这个命令运行了两次，第一次报错了

另外，重启桌面版之后，这个新建的数据库在对应的DBMS界面没有显示，但是用命令行去看，是可以看到的。

CALL db.schema.visualization()

可以看到，neo4j中的schema导入前mysql的schema是基本一致的（只是关系表，转为了relationship边）

match (p:Poem)
return p

一个表的主码就作为节点，其他的属性就作为节点的属性。

2 MySQL中实现的规定

根据参考，进行关键部分的摘录

2.1 基本概念

MySQL支持外键，允许跨表的交叉引用（外键可以是表内，也可以是表外，mysql都支持），外键约束可以帮助保持相关数据的一致性。（被参照表的主键字段删了，对应的参照表的外键也会删掉）

外键关系包含拥有列的初始值的父表（例如：学生表），以及引用父表中列的值的子表（例如：学生成绩表引用学生表的student_id）。外键约束定义在子表上

创建表或者修改表时进行外键约束的语法如下（只有这两种时候才可以加外键约束）：

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

2.2 ✅ 命名规则

• 在外键约束的语法中，如果已经对外键命名，即上面的[CONSTRAINT [symbol]] FOREIGN KEY中symbol这个字段有值了，则直接使用这个值作为外键名称
• 如果CONSTRAINT symbol子句未定义，或CONSTRAINT关键字后未包含符号，
  • 对于InnoDB表，会自动生成约束名称，ibfk是InnoDB Foreign Key的缩写
  • 例如：使用Navicat工具管理mysql数据库。外键的一种常用命名方式是表名_fk_序号，如下，poet表的第一个外键就是：poet_ibfk_1
    
  • 对于NDB表，使用该 FOREIGN KEY index_name值（如果已定义）。否则，会自动生成约束名称。
• 该值（如果已定义）在数据库中必须是唯一的。重复会 导致类似于以下内容的错误：ERROR 1005 (HY000): Can't create table 'test.fk1' (errno: 121)。 CONSTRAINT symbolsymbol(外键名称在一个数据库中必须唯一)

这是Mysql错误1452 - Cannot add or update a child row: a foreign key constraint fails怎么办？这个错误下这个答案的依据，只是不太匹配这个报错信息

2.3 ✅ 条件和限制

外键约束遵循以下条件和限制：

• 父表和子表必须使用相同的存储引擎，同时子表和父表不能是临时表。

  • 这是Mysql错误1452 - Cannot add or update a child row: a foreign key constraint fails怎么办？这个错误下这个答案的依据

• 创建外键需要从父表得到 REFERENCES 权限（需要有引用的权限）

• 外键列和它引用的被参照列必须有相似的数据类型（最好是一模一样或者是可以兼容的），比如整数（INTEGER）和小数（DECIMAL）的符号以及固定精度必须一致；如果是字符串类型，则对长度的规定可以不一致，对于非二进制（字符）字符串列，字符集和排序规则必须相同。

  • 类似下面这样

• MySQL支持外键引用同一个表中的另一列（主键），但是列不能对列自身有引用。在这种情况下，子表记录指的是同一个表中的依赖记录。

  • 比如，如果学生组，包含学生id属性，小组长/班长id属性，那么后者其实就会引用前者，所以支持同一个表中不同列的引用，但是不支持列对列自身的引用

• MySQL要求外键和引用的列上都有索引，这样进行外键检查的时候就不需要扫描整个表，可以加快速度。在引用表中，外键引用的那列必须要用索引，如果没有，就会自动创建。

• 对于InnoDB来说，允许外键引用含有索引的列或者列组（好几列），不过，在引用的数据表上，其中引用的第一列上必须有相同的索引。

  • 关于索引这部分，其实可以观察一下，在创建外键之后，会自动对外键的列添加索引。（确实是引用表的外键上有索引，被引用的则没有）
    

• 外键关系中的表不能更改为使用另一个存储引擎。要更改存储引擎，您必须首先删除所有外键约束。简单来说，还是建立外键关系的两个表，必须使用同一种存储引擎

2.3.1 存储引擎查询

• 关于存储引擎，其实就是这个：
  
• 也可以使用sql语句查询某个表的存储引擎

  # 查询某个数据库中所有表的存储引擎
  show table status from 库名；
  # 查询某个特定表的存储引擎
  show table status from 库名 where name='表名'；
  

  

参考：

• mysql官方文档：13.1.18.5 FOREIGN KEY Constraints
• mysql官方文档：1.7.2.3 FOREIGN KEY Constraint Differences

✅mysql5.7文档中文翻译（显示的是5.7 但是对应章节都不一样，参考对照着看，另外，有点像机翻，哈哈。）

• 13.1.18.5 外键约束

• 1.8.2.3 FOREIGN KEY 约束差异

• MySQL自学笔记——设置外键约束这个应该也是看官方文档翻译的，可以作为参考

其他可能有用的信息

• SQL FOREIGN KEY 约束
• MySQL外键约束（FOREIGN KEY）

mysql5.1文档的中文翻译

• 1.8.6. MySQL处理约束的方式，网页中的链接有错误，自己往下拉，或者直接搜索外键
• 15.2.6.4.外键约束
• 23.1.12. INFORMATION_SCHEMA TABLE_CONSTRAINTS表

实践的结果（从程序员的角度来看）：

• 父表中的非主键 可以作为子表外键吗？ 理论依据何在？
• 一个表的字段不是主键可以做其他表的外键么

现有两张表：patrol（主表）、people（子表）。
patrol主表

people子表

people子表设置外键

数据：
patrol主表

people子表（pid是主表ID）

当我们设置了外键时，删除主表数据的同时会连同子表数据一并删除。

DELETE FROM patrol WHERE id = 1

执行语句后patrol主表id为1的数据被删除了，然后people子表pid为1的数据也删除了。