SQLで外部キーを使用する方法
著者は、 Apache Software Foundation を選択して、 Write forDOnationsプログラムの一環として寄付を受け取りました。
序章
大規模なSQLプロジェクトで作業する場合は、外部キーを使用してすべてのテーブルでデータの正確性と一貫性を維持する必要があります。 外部キーは、2つのテーブルのデータ間のリンクを提供するリレーショナルデータベーステーブルの列または列のグループです。 このユースケースでは、ここで参照整合性が機能します。 たとえば、job_titlesという名前のルックアップテーブルを参照するjob_title_idという名前の列を持つemployeesテーブルを持つことができます。
別の例は、親products_categoriesテーブルにリンクするproductsテーブルにcategory_id列を作成するeコマースデータベースで示すことができます。
参照整合性は、すべてのデータ参照が有効であることを保証し、一貫性のないエントリや孤立したレコードを防ぎます。 参照整合性は、マルチユーザーデータベース環境での無効なデータの入力を防ぐのにも役立ちます。
このガイドでは、データベースの外部キーを使用して参照整合性を適用します。 このガイドはMySQLデータベースでテストされていますが、構文を少し変更するだけで、他のSQLベースのデータベースでも機能します。
前提条件
このチュートリアルを完了するには、次のものが必要です。
- 非ルート
sudoユーザーと基本的なファイアウォールで保護されたUbuntu20.04サーバー。 Ubuntu 20.04の初期サーバーセットアップガイドに従って、root以外のユーザーを作成し、ファイアウォールを有効にします。 - MySQLデータベースサーバー。 MySQLをUbuntu20.04サーバーにインストールする方法のチュートリアルを確認して、データベースサーバーをセットアップおよび構成します。
ステップ1—サンプルデータベースとテーブルの設定
このステップでは、サンプルデータベースを作成し、いくつかのテーブルを設定します。 また、ガイド全体で外部キーを操作するために使用するサンプルデータをいくつか挿入します。
root以外のユーザーとしてサーバーに接続することから始めます。 次に、次のコマンドを実行してMySQLサーバーにログインします。 example_userをroot以外のアカウントの正確な名前に置き換えます。
sudo mysql -u example_user -p
プロンプトが表示されたら、MySQLサーバーのroot以外のユーザーアカウントのパスワードを入力し、ENTERまたはRETURNを押して続行します。 次に、次のSQLコマンドを発行して、サンプルのcompany_dbデータベースを作成します。
CREATE DATABASE company_db;
次の出力を確認して、データベースがエラーなしで作成されていることを確認します。
OutputQuery OK, 1 row affected (0.01 sec)
出力にエラーメッセージが表示されずにデータベースが正常に作成されたら、SQL USEキーワードを適用して、新しいcompany_dbデータベースに切り替えます。
USE company_db;
company_dbデータベースに正常に切り替えたことを示す次の確認が表示されます。
OutputDatabase changed
次に、CREATE TABLEコマンドを使用してjob_titlesテーブルを設定します。 このテーブルは、データベースで使用可能なすべての役職のルックアップテーブルとして機能します。 job_title_idは、最大2^63-1レコードに対応できるBIGINTデータ型を使用して、データベース内の各役職を一意に識別する主キーです。 AUTO_INCREMENTキーワードを使用して、新しい役職を挿入するたびにMySQLに連続する数値を自動的に割り当てさせています。
CREATE TABLEコマンドに、役職の人間が読める形式の値を格納するjob_title_name列を含めます。 この列には、50文字の最大長の文字列値が格納されます。 このデータ型は、構文VARCHAR(50)で定義します。
CREATE TABLEコマンドに続いて、ENGINE = InnoDBキーワードを含めて、InnoDBデータベースエンジンを使用するようにMySQLに指示します。 これは、データベースアプリケーションで高い信頼性と高いパフォーマンスを確保しながら、同時実行性を処理するトランザクション対応の汎用ストレージエンジンです。
次のコマンドを実行して、job_titlesテーブルを作成します。
CREATE TABLE job_titles (
job_title_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
job_title_name VARCHAR(50)
) ENGINE = InnoDB;
CREATE TABLE job_titles...ステートメントを実行した後、次の出力を確認して、コマンドが正常に完了したことを確認してください。
OutputQuery OK, 0 rows affected (0.03 sec)
これで、サンプル会社で利用可能なすべての有効なポジションのルックアップテーブルができました。 次に、いくつかのサンプル位置をjob_titlesテーブルに挿入します。
INSERT INTO job_titles (job_title_name) VALUES ('BRANCH MANAGER');
INSERT INTO job_titles (job_title_name) VALUES ('CLERK');
INSERT INTO job_titles (job_title_name) VALUES ('LEVEL 1 SUPERVISOR');
各コマンドの後に、次の確認メッセージが表示されます。
OutputQuery OK, 1 row affected (0.00 sec) ...
使用可能な役職を挿入したので、MySQL SELECTキーワードを使用してjob_titlesテーブルにクエリを実行し、データを確認します。
SELECT job_title_id, job_title_name FROM job_titles;
これで、以下に示すように、使用可能なすべてのポジションのリストが表示されます。
Output+--------------+--------------------+ | job_title_id | job_title_name | +--------------+--------------------+ | 1 | BRANCH MANAGER | | 2 | CLERK | | 3 | LEVEL 1 SUPERVISOR | +--------------+--------------------+ 3 rows in set (0.00 sec)
次に、employeesテーブルを作成します。 このテーブルには、会社のすべてのスタッフのレコードが保持されます。 employeesテーブルのjob_title_id列は、job_titlesテーブルの同じ列を指します。 これは、ステートメントFOREIGN KEY (job_title_id) REFERENCES job_titles (job_title_id)を発行することで実現しています。 一貫性を保つために、関連する列に使用したBIGINTデータ型を使用しています。
次のemployeesテーブルでは、employees_idはPRIMARY KEYであり、AUTO_INCREMENTキーワードを使用して新しいemployees_idsを生成しました。新しい値を挿入します。
最大長50文字のfirst_nameおよびlast_nameテキストフィールドを使用して従業員の名前をキャプチャしています。 このデータ型は、電話番号にも最適です。 したがって、VARCHAR(50)データ型は、first_name、last_name、およびphoneフィールドで機能するはずです。
2つの相互リンクされたテーブルからデータを取得する際の速度を向上させるには、ステートメントINDEX (job_title_id)を使用してjob_title_id列にインデックスを付けます。 繰り返しになりますが、ステップ1 で概説されているように、InnoDBストレージエンジンを利用するには、キーワードENGINE = InnoDBを必ず含めてください。
employeesテーブルを作成するには、次のコマンドを実行します。
CREATE TABLE employees (
employee_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
job_title_id BIGINT NOT NULL,
first_name VARCHAR(50),
last_name VARCHAR(50),
phone VARCHAR(50),
INDEX (job_title_id),
FOREIGN KEY (job_title_id) REFERENCES job_titles (job_title_id)
) ENGINE = InnoDB;
テーブルを作成したことを確認する次の出力が表示されることを確認してください。
OutputQuery OK, 0 rows affected (0.04 sec)
テスト目的で適切なデータベースとテーブルを設定したので、次に、テーブルにデータを挿入するときに次に何が発生するかを確認します。
ステップ2—無効なデータを挿入する
このステップでは、孤立したレコードをemployeesテーブルに挿入します。 この場合の孤立したレコードは、無効なjob_title_idsを持つレコードです。 job_titlesテーブルから、以下に示すように、有効な役職は3つだけです。
BRANCH MANAGERCLERKLEVEL 1 SUPERVISOR
次に、次のINSERTステートメントを実行して、employeesテーブルに無効なレコードを追加してみてください。
INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (4, 'JOHN', 'DOE', '11111'); INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (15, 'MARY', 'SMITH', '22222'); INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (7, 'JANE', 'MIKE', '33333');
4、15、および7は無効なjob_title_idsであるため、上記のINSERTステートメントはすべて失敗し、次のエラーが表示されます。
OutputERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company_db`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`job_title_id`) REFERENCES `job_titles` (`job_title_id`)) ...
次のステップでは、有効なデータをemployeesテーブルに入力し、コマンドが成功するかどうかを確認します。
ステップ3—有効なデータを挿入する
テーブルが外部キーと相互リンクされている場合に、参照整合性が無効なデータの入力をどのように防ぐかを見てきました。 つまり、外部キーを使用すると、外部クライアントアプリケーションでそのビジネスロジックをコーディングしなくても、データベースを一貫した状態に保つことができます。
このステップでは、有効なデータを挿入し、挿入が成功するかどうかを確認します。 次のコマンドを実行します。
INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (2, 'PETER', 'SMITH', '55555'); INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (1, 'JOHN', 'DOE', '11111'); INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (2, 'STEVE', 'KIM', '66666'); INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (3, 'MARY', 'ROE', '22222'); INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (2, 'JANE', 'MIKE', '33333');
有効なjob_title_idsを挿入すると、INSERTステートメントが成功します。 各INSERTコマンドを実行すると、次の出力が表示されます。
OutputQuery OK, 1 row affected (0.00 sec) ...
ここまでで、参照整合性を実装することは、データを検証し、相互リンクされたテーブルを操作するときに存在しないレコードの入力を防ぐための便利なアプローチであることに気付くでしょう。 また、外部キーを使用することで、相互リンクされたデータを効率的にクエリできる最適化されたデータベースを作成しています。
たとえば、役職名が綴られているすべてのスタッフのレコードを取得するには、employeesテーブルとjob_titlesテーブルに対して次のJOINステートメントを実行します。
SELECT employee_id, employees.job_title_id, job_titles.job_title_name, first_name, last_name, phone FROM employees LEFT JOIN job_titles ON employees.job_title_id = job_titles.job_title_id;
以下の出力では、各従業員の情報が、関連する役割/役職とともに表示されています。
Output+-------------+--------------+--------------------+------------+-----------+-------+ | employee_id | job_title_id | job_title_name | first_name | last_name | phone | +-------------+--------------+--------------------+------------+-----------+-------+ | 5 | 1 | BRANCH MANAGER | JOHN | DOE | 11111 | | 4 | 2 | CLERK | PETER | SMITH | 55555 | | 6 | 2 | CLERK | STEVE | KIM | 66666 | | 8 | 2 | CLERK | JANE | MIKE | 33333 | | 7 | 3 | LEVEL 1 SUPERVISOR | MARY | ROE | 22222 | +-------------+--------------+--------------------+------------+-----------+-------+ 5 rows in set (0.00 sec)
上記の出力からわかるように、BRANCH MANAGERが1つ、CLERKが3つ、LEVEL 1 SUPERVISORが1つあります。
リンクされた子テーブルで外部キーによってすでに参照されている親レコードの削除を防ぐ場合にも、外部キーは優れています。 これを適用できる実際の例をいくつか示します。
- eコマースWebサイトでは、
salesテーブルで顧客のアクティブな注文を行ったときに、customersテーブルから顧客の詳細が誤って削除されるのを防ぐことができます。 - ライブラリシステムでは、学生が
issued_booksテーブルにレコードを関連付けている場合に、registersテーブルから学生を削除しないようにすることができます。 - 銀行では、クライアントが
savings_accounts_transactionsテーブルですでにいくつかの入出金を行っている場合に、外部キーアプローチを使用して、savings_accountsテーブルからレコードを削除しないようにすることができます。
同様に、テーブル内のデータの削除を試みることができます。 コマンドライン端末で、job_titlesテーブルから1つの位置を削除します。
DELETE FROM job_titles WHERE job_title_id = 1 ;
employeesテーブルにBRANCH MANAGERというタイトルのレコードを既に挿入しているため、DELETEステートメントは失敗し、次のエラーが表示されます。
OutputERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`company_db`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`job_title_id`) REFERENCES `job_titles` (`job_title_id`))
ここでも、job_titlesテーブルに新しい役割を追加します。
INSERT INTO job_titles (job_title_name) VALUES ('CEO');
コマンドを正常に実行すると、次の出力が表示されます。
OutputQuery OK, 1 row affected (0.00 sec) ...
ここでも、job_titlesテーブルをクエリして、新しい位置のjob_title_idを確認します。
SELECT job_title_id, job_title_name FROM job_titles;
これで、以下に示すように、使用可能なすべてのポジションのリストが表示されます。 CEOロールには、4のjob_title_idがあります。
Output+--------------+--------------------+ | job_title_id | job_title_name | +--------------+--------------------+ | 1 | BRANCH MANAGER | | 2 | CLERK | | 3 | LEVEL 1 SUPERVISOR | | 4 | CEO | +--------------+--------------------+ 4 rows in set (0.00 sec)
これで、テーブルに4つの行があります。 次に、employeesテーブルに関連するレコードを入力する前に、4のjob_title_idで新しい役割を削除します。
DELETE FROM job_titles WHERE job_title_id = 4 ;
DELETEステートメントが成功するはずです。
OutputQuery OK, 1 row affected (0.00 sec)
上記のすべてのテストをエラーなしで完了した後、外部キーが期待どおりに機能していることが明らかになりました。
結論
このガイドでは、相互リンクされたテーブルを使用してサンプルデータベースを設定し、リレーショナルデータベース管理システムでの参照整合性の使用を実践しました。 データベースを不整合な状態にするデータの削除を検証および防止する上で、外部キーがいかに重要であるかを見てきました。 このガイドの知識を次のデータベースプロジェクトに使用して、外部キーを活用してください。
MySQLデータベースの詳細については、次のチュートリアルを確認してください。
