【跟着例子学MySQL】 补充内容 -- 主外键和索引

---

前言

举例子，是最简单有效的学习方法。本系列文章以一个贯穿始终的场景，结合多个实例讲解MySQL的基本用法。

❔ 为什么要写这个系列？

• 模仿是最好的老师，实践是检验成果的方法。
• 本系列以实操样例和应用场景为核心，将MySQL基本用法贯穿其中，达到学以致用的效果。

❔ 为什么要学习MySQL？

• MySQL是最常用的数据库之一，具有简单易用的特点，适合初学者学习
• 数据库的基本用法有相通之处，学会MySQL可以为学习其他数据库打下良好基础

❔ 跟别的入门教材有什么不同？

• 以一个贯穿始终的应用场景为主线，渐进地讲解用法
• 难度适中，既有基础方法，也有值得注意的关键细节

❔ 本系列文章不包含哪些内容？

该系列文章的目标是，将最核心的知识，以最直接的方式，呈现给初学者。因此不包含：

• MySQL安装方法
• MySQL系统管理方法，例如备份、恢复、导入导出等
• 高级主题，例如数据库监控、数据库调优和SQL优化

---

回顾

上篇文章👉《【跟着例子学MySQL】多表关联 – 一对一关系》 讲了一对一的多表关联。这篇讲解关于主键、外键和索引的更多知识。

---

主键

在关系模型中，表不应包含重复的行，因为这会在检索中产生歧义。为了确保唯一性，每个表都应该有一个列（(或一组列），称为主键，它可以唯一地标识表中的每个记录。例如，一个唯一的 customerID可以作为客户表的主键；产品表的productCode；图书表的isbn。如果主键是单列，则称为简单键；如果它由几列组成，则称为复合键。大多数RDBMS会在主键上建立一个索引，以方便快速搜索。主键通常用于与其他表进行关联。

外键

子表的外键用于引用父表。可以施加外键约束，以确保子表中的所谓引用完整性值必须是父表中的有效值。

我们在定义引用父表的子表时定义外键，如下所示：

-- 子表定义
CREATE TABLE tableName (
    ......
    ......
    CONSTRAINT constraintName FOREIGN KEY (columName) REFERENCES parentTableName     (columnName)
    [ON DELETE RESTRICT | CASCADE | SET NULL | NO ACTION] 
    [ON UPDATE RESTRICT | CASCADE | SET NULL | NO ACTION] 
)

你可以通过可选的ON UPDATE和ON DELETE子句来指定更新和删除的引用：

1. RESTRICT（默认）：如果子表中存在匹配的行，则不允许删除或更新父行。
2. CASCADE：将删除或更新操作级联到子表中匹配的行。
3. SET NULL：将子表中的外键值设置为NULL（如果允许为NULL）。
4. NO ACTION：表示对父行没有操作。

尝试删除 products_suppliers（子）表所引用的suppliers（父）表中的记录，例如，

mysql> SELECT * FROM products_suppliers;
+-----------+------------+
| productID | supplierID |
+-----------+------------+
| 2001      | 501        |
| 2002      | 501        |
| 2003      | 501        |
| 2004      | 502        |
| 2001      | 503        |
+-----------+------------+
-- 尝试从父表中删除与子表中的行相匹配的行
mysql> DELETE FROM suppliers WHERE supplierID = 501;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`productsales`.`products_suppliers`, CONSTRAINT `products_suppliers_ibfk_2`
FOREIGN KEY (`supplierID`) REFERENCES `suppliers` (`supplierID`))

无法删除该记录，因为已施加了默认的“ON DELETE RESTRICT”约束。

索引

可以在选定的列上创建索引（或键），以方便快速搜索。在没有索引的情况下，SELECT * FROM products WHERE productID=x需要与products中所有记录的productID列相匹配。如果对productID列进行索引（例如，使用二叉树），匹配可以大大改进（通过二叉树搜索）。

你应该索引在WHERE子句中常用的列；并作为JOIN列。

索引的缺点是成本和空间。构建和维护索引需要计算和内存空间。索引便于快速搜索，但降低修改表（插入/更新/删除）的性能，因为需要验证。然而，关系数据库通常为查询和检索进行优化，但不是为更新进行优化。

在MySQL中，关键字KEY是索引的同义词。

在MySQL中，索引可以建立在：

1. 单列（列索引）
2. 一组列（组合索引）
3. 唯一值列（唯一索引或唯一键）
4. 字符串（VARCHAR或CHAR）的列前缀，例如前5个字符

在一个表中可以有多个索引。索引会自动建立在主键列上。你可以通过创建表、创建索引或更改表来构建索引。

CREATE TABLE tableName (
  ......
  [UNIQUE] INDEX|KEY indexName (columnName, ...),
  -- 可选关键字UNIQUE确保此列中的所有值都是不同. KEY是索引的同义词
  ......
PRIMAY KEY (columnName, ...) -- 自动建立在主键列上的索引
);
CREATE [UNIQUE] INDEX indexName ON tableName(columnName, ...);
ALTER TABLE tableName ADD UNIQUE|INDEX|PRIMARY KEY indexName (columnName, ...)
SHOW INDEX FROM tableName;

例如：

mysql> CREATE TABLE employees (
         emp_no INT UNSIGNED NOT NULL AUTO_INCREMENT,
         name VARCHAR(50) NOT NULL,
         gender ENUM ('M','F') NOT NULL,
         birth_date DATE NOT NULL,
         hire_date DATE NOT NULL,
         PRIMARY KEY (emp_no) -- 在主键列上自动构建的索引
       );
mysql> DESCRIBE employees;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| emp_no     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(50)      | NO   |     | NULL    |                |
| gender     | enum('M','F')    | NO   |     | NULL    |                |
| birth_date | date             | NO   |     | NULL    |                |
| hire_date  | date             | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
mysql> SHOW INDEX FROM employees \G
*************************** 1. row ***************************
Table: employees
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: emp_no
.......
mysql> CREATE TABLE departments (
         dept_no CHAR(4) NOT NULL,
         dept_name VARCHAR(40) NOT NULL,
         PRIMARY KEY (dept_no), -- 在主键列上自动构建的索引
         UNIQUE INDEX (dept_name) -- 在此唯一值列上构建索引
      );
mysql> DESCRIBE departments;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_no   | char(4)     | NO   | PRI | NULL    |       |
| dept_name | varchar(40) | NO   | UNI | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
mysql> SHOW INDEX FROM departments \G
*************************** 1. row ***************************
Table: departments
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: dept_no
.......
*************************** 2. row ***************************
Table: departments
Non_unique: 0
Key_name: dept_name
Seq_in_index: 1
Column_name: dept_name
.......
-- 员工与部门之间的多对多连接表
mysql> CREATE TABLE dept_emp (
         emp_no INT UNSIGNED NOT NULL,
         dept_no CHAR(4) NOT NULL,
         from_date DATE NOT NULL,
         to_date DATE NOT NULL,
         INDEX (emp_no), -- 在此非唯一值列上构建索引
         INDEX (dept_no), -- 在此非唯一值列上构建索引
         FOREIGN KEY (emp_no) REFERENCES employees (emp_no)
         ON DELETE CASCADE ON UPDATE CASCADE,
         FOREIGN KEY (dept_no) REFERENCES departments (dept_no)
         ON DELETE CASCADE ON UPDATE CASCADE,
         PRIMARY KEY (emp_no, dept_no) -- 自动生成的索引
      );
mysql> DESCRIBE dept_emp;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| emp_no    | int(10) unsigned | NO   | PRI | NULL    |       |
| dept_no   | char(4)          | NO   | PRI | NULL    |       |
| from_date | date             | NO   |     | NULL    |       |
| to_date   | date             | NO   |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
mysql> SHOW INDEX FROM dept_emp \G
*************************** 1. row ***************************
Table: dept_emp
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: emp_no
........
*************************** 2. row ***************************
Table: dept_emp
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: dept_no
........
*************************** 3. row ***************************
Table: dept_emp
Non_unique: 1
Key_name: emp_no
Seq_in_index: 1
Column_name: emp_no
........
*************************** 4. row ***************************
Table: dept_emp
Non_unique: 1
Key_name: dept_no
Seq_in_index: 1
Column_name: dept_no
........

---

未完待续

下一篇我们接着介绍SQL的高级用法。

---

如果喜欢这篇文章，请不要忘记关注🧡、点赞👍和收藏📔哦！