数据库外键理论及MySQL外键实现规定

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中某个值。

1.3 用户定义完整性

1.4 ✅ 参照完整性的SQL实现

1.5 ✅ MySQL数据库操作示例

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

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

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

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

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(外键名称在一个数据库中必须唯一)

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表

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

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