## モジュール 2: 外部キー (v8.5.0) 外部キーは従来のRDBMSでは一般的ですが、分散データベースではほとんど見られません。多くの分散データベースは外部キーを実装しておらず、データベース内のデータ整合性を維持することが課題となっています。しかし、分散データベースとして、TiDBは外部キーをサポートしています。 TiDBの外部キー制約を使用すると、テーブル間の関係を定義し、これらの関係を利用してデータの整合性を強制できます。基本的なレベルでは、外部キー制約は、子テーブルの列セットが親テーブルの列セットに対応し、整合性を壊す可能性のある操作を禁止することを保証します。注文テーブルと顧客テーブルの例を考えてみましょう。注文テーブルの外部キー制約は、すべての注文が既存の顧客に対応することを保証し、データの正確性と整合性を維持します。 ### 手順 1. bookshop スキーマを作成し、TiDB SQL レイヤーを介してデータをロードする + [ターミナル 1](ssh://monitor1-ssh#1) で、TiDBサーバーインスタンスに接続します。 ```bash mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root ``` ```output mysql> ``` + `mysql>`プロンプトで次のコマンドを実行して、データベースオブジェクトを作成し、ブックショップスキーマのデータをロードします。このスクリプトは`bookshop`というスキーマを作成します。 ```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コマンドを使用して、スキーマを探索し、データベース内のオブジェクトを説明できます。これは、任意の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. 外部キーで参照整合性を強制する + 例のブックショップスキーマでは、モジュール1でスキーマ作成中に外部キーを追加しませんでした。次に、以下の図に示すエンティティ関係を強制する必要があります。 ```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.auth_id`と`authors.id`に対して`book_authors`と`authors`の間の参照制約を強制するために、次のコマンドを実行します。`mysql>`プロンプトでTerminal 1で実行します。 ```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の著者が存在しないため、**エラー**が予想されます。 ```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クライアント接続を開いたままにして、次のモジュールに進みます。