## Module 2: Foreign Key (v8.5.0) Foreign keys are common in traditional RDBMS, but they are rarely seen in distributed databases. Many distributed databases fail to implement foreign keys, which presents a challenge for maintaining in-database data integrity. However, as a distributed database, TiDB supports foreign keys. Foreign Key constraints in TiDB allow you to define relationships between tables, utilizing these relationships to enforce data integrity. At a basic level, a Foreign Key constraint ensures that a set of columns in a child table corresponds to a set of columns in a parent table, prohibiting actions that might break the link. Consider a table of orders and a table of customers; a Foreign Key constraint on the orders table could ensure that every order corresponds to an existing customer, thus maintaining data accuracy and integrity. ### Step 1. Create a bookshop schema and load data via TiDB SQL layer + On [Terminal 1](ssh://monitor1-ssh#1) , connect to TiDB server instance. ```bash mysql -h ${HOST_TIPROXY1_PRIVATE_IP} --port 6000 -u root ``` ```output mysql> ``` + Run the following command in `mysql>` prompt to create the database objects and load data for our book shop schema. The script creates a schema called `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) ``` + The following is an excerpt of the contents of the `bookshop_demo.sql` script in the previous step, showing the creation of the tables and indexes for our book shop application. You don't need to run these statements now, since they were already executed, but you can see how we've modeled our data for our application. It's worth noting that you'll find that except for `AUTO_RANDOM`, all the declarations are just like those used in 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`) ); ``` + You can explore the schema and describe the objects in the database with mysql commands, just as you would with any MySQL database. ```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) ``` + In the `mysql>` prompt, run the following query to see a few books records. ```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) ``` ### Step 2. Enforce the referential integrity with a foreign key + As in our example bookshop schema, we didn't add the foreign keys during the schema creation in module 1. Now, we need to enforce the entity relationship illustrated in the following diagram. ```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 } ``` + Execute the following command to enforce the referencing constraint between `book_authors` and `authors` on `book_authors.auth_id` and `authors.id`. Let's execute it on [Terminal 1](ssh://monitor1-ssh#1) under the `mysql>` prompt. ```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) ``` + Try to add a record into `book_authors` which violates the foreign key constraint you just declared. An **ERROR** is expected because author with `auth_id` 99999999 is not exist. ```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`)) ``` + Try to add a record into `book_authors` which consistent with the foreign key constraint you just declared. An successful insertion is expected. For example, declaring Dr. Lindsey Welch II (an existing author with author id 1) is also an author for the book "Till The End of Time" (an existing book with book id 100). ```sql INSERT INTO bookshop.book_authors (book_id, author_id) VALUES (100, 1); ``` ```output Query OK, 1 row affected (0.00 sec) ``` + At this point, you have experienced the foreign key constraint capability on a distributed database - TiDB. Keep the mysql client connection open, and proceed to the next module.