## 模块 2: 外键（v8.5.0） 外键在传统的 RDBMS 中很常见，但在分布式数据库中很少见。许多分布式数据库未能实现外键，这给维护数据库内数据完整性带来了挑战。然而，作为一个分布式数据库，TiDB 支持外键。 TiDB 中的外键约束允许您定义表之间的关系，利用这些关系来强制执行数据完整性。在基本层面上，外键约束确保子表中的一组列对应于父表中的一组列，禁止可能破坏链接的操作。考虑一个订单表和一个客户表；订单表上的外键约束可以确保每个订单对应一个现有客户，从而维护数据准确性和完整性。 ### 步骤 1. 创建一个 `bookshop` schema 并通过 TiDB SQL 层加载数据 + 在[终端 1](ssh://monitor1-ssh#1) 上，连接到 TiDB 服务器实例。 ```bash mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root ``` ```output mysql> ``` + 在`mysql>`提示符下运行以下命令来创建数据库对象并为我们的书店 schema 加载数据。该脚本创建一个名为 `bookshop` 的 schema。 ```sql SOURCE bookshop_demo.sql SHOW TABLES; ``` ```output Query OK, 0 rows affected (0.08 sec) ... +--------------------+ | Tables_in_bookshop | +--------------------+ | authors | | book_authors | | books | | orders | | ratings | | users | +--------------------+ 6 rows in set (0.00 sec) ``` + 以下是上一步中 `bookshop_demo.sql` 脚本内容的摘录，显示了我们书店应用程序的表和索引的创建。您现在不需要运行这些语句，因为它们已经执行过了，但您可以看到我们如何为应用程序建模数据。值得注意的是，您会发现除了 `AUTO_RANDOM` 之外，所有声明都与 MySQL 中使用的相同。 ```output CREATE TABLE `authors` ( `id` bigint(20) AUTO_RANDOM, `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `gender` tinyint(1) DEFAULT NULL, `birth_year` smallint(6) DEFAULT NULL, `death_year` smallint(6) DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `book_authors` ( `book_id` bigint(20) NOT NULL, `author_id` bigint(20) NOT NULL, PRIMARY KEY (`book_id`, `author_id`) ); CREATE TABLE `books` ( `id` bigint AUTO_RANDOM, `title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `type` enum( 'Magazine', 'Novel', 'Life', 'Arts', 'Comics', 'Education & Reference', 'Humanities & Social Sciences', 'Science & Technology', 'Kids', 'Sports' ) COLLATE utf8mb4_unicode_ci NOT NULL, `published_at` datetime NOT NULL, `stock` int DEFAULT '0', `price` decimal(15, 2) DEFAULT '0.0', PRIMARY KEY (`id`) ) PARTITION BY HASH (`id`) PARTITIONS 8; CREATE TABLE `orders` ( `id` bigint(20) NOT NULL, `book_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `quality` tinyint(4) NOT NULL, `ordered_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `orders_book_id_idx` (`book_id`) ); CREATE TABLE `ratings` ( `book_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `score` tinyint(4) NOT NULL, `rated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY `uniq_book_user_idx` (`book_id`, `user_id`), PRIMARY KEY (`book_id`, `user_id`) ); CREATE TABLE `users` ( `id` bigint(20) AUTO_RANDOM, `balance` decimal(15, 2) DEFAULT '0.0', `nickname` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `nickname` (`nickname`) ); ``` + 您可以使用 mysql 命令探索 schema 并描述数据库中的对象，就像使用任何 MySQL 数据库一样。 ```sql DESC bookshop.books; ``` ```output +--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+------+------+---------+-------+ | id | bigint(20) | NO | PRI | NULL | | | title | varchar(100) | NO | | NULL | | | type | enum('Magazine','Novel','Life','Arts','Comics','Education & Reference','Humanities & Social Sciences','Science & Technology','Kids','Sports') | NO | | NULL | | | published_at | datetime | NO | | NULL | | | stock | int(11) | YES | | 0 | | | price | decimal(15,2) | YES | | 0.0 | | +--------------+-----------------------------------------------------------------------------------------------------------------------------------------------+------+------+---------+-------+ 6 rows in set (0.00 sec) ``` + 在 `mysql>` 提示符下，运行以下查询以查看一些书籍记录。 ```sql SELECT * FROM bookshop.books ORDER BY title LIMIT 3; ``` ```output +----+-------------------------------+------------------------------+---------------------+--------+-------+ | id | title | type | published_at | stock | price | +----+-------------------------------+------------------------------+---------------------+--------+-------+ | 43 | (I've Got a Gal In) Kalamazoo | Sports | 2006-05-22 05:19:49 | 231321 | 85.72 | | 88 | (Let Me Be Your) Teddy Bear | Sports | 2003-04-08 08:37:52 | 140 | 22.14 | | 40 | (You Keep Me) Hangin' On | Humanities & Social Sciences | 2005-02-12 05:29:04 | 163 | 32.72 | +----+-------------------------------+------------------------------+---------------------+--------+-------+ 3 rows in set (0.05 sec) ``` ### 步骤 2. 使用外键强制引用完整性 + 在我们的示例 bookshop schema 中，我们在模块 1 创建 schema 时没有添加外键。现在，我们需要强制执行下图所示的实体关系。 ```mermaid --- title: Book Authors Example --- erDiagram AUTHORS ||--|{ BOOK_AUTHORS : references AUTHORS { BIGINT id PK VARCHAR name TINYINT gender SMALLINT birth_year SMALLINT death_year } BOOK_AUTHORS { BIGINT book_id PK BIGINT author_id PK,FK } ``` + 执行以下命令在 `book_authors` 和 `authors` 表之间建立引用约束，约束 `book_authors.auth_id` 和 `authors.id` 字段。让我们在[终端 1](ssh://monitor1-ssh#1) 的 `mysql>` 提示符下执行此命令。 ```sql ALTER TABLE bookshop.book_authors ADD CONSTRAINT book_author_fk FOREIGN KEY (author_id) REFERENCES bookshop.authors (id); ``` ```output Query OK, 0 rows affected (0.10 sec) ``` + 尝试向 `book_authors` 添加一条违反外键约束的记录。由于 `auth_id` 为 99999999 的作者不存在，预期会出现 **ERROR**。 ```sql INSERT INTO bookshop.book_authors (book_id, author_id) VALUES (100, 99999999); ``` ```output ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bookshop`.`book_authors`, CONSTRAINT `book_author_fk` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`)) ``` + 尝试向 `book_authors` 添加一条符合外键约束的记录。预期会插入成功。例如，声明 Dr. Lindsey Welch II（一个已存在的作者，作者 ID 为 1）也是《Till The End of Time》（一本已存在的书，书籍 ID 为 100）的作者。 ```sql INSERT INTO bookshop.book_authors (book_id, author_id) VALUES (100, 1); ``` ```output Query OK, 1 row affected (0.00 sec) ``` + 至此，您已经体验了分布式数据库 TiDB 的外键约束功能。请保持 mysql 客户端连接打开，然后继续进入下一个模块。