SQLで外部キーを使用する方法
著者は、 Apache Software Foundation を選択して、 Write forDOnationsプログラムの一環として寄付を受け取りました。
序章
大規模なSQLプロジェクトで作業する場合は、外部キーを使用してすべてのテーブルでデータの正確性と一貫性を維持する必要があります。 外部キーは、2つのテーブルのデータ間のリンクを提供するリレーショナルデータベーステーブルの列または列のグループです。 このユースケースでは、ここで参照整合性が機能します。 たとえば、 employees
名前の付いた列を持つテーブル job_title_id
これは、という名前のルックアップテーブルを参照します job_titles
.
別の例は、eコマースデータベースで実証できます。 category_id
の列 products
親にリンクするテーブル products_categories
テーブル。
参照整合性は、すべてのデータ参照が有効であることを保証し、一貫性のないエントリや孤立したレコードを防ぎます。 参照整合性は、マルチユーザーデータベース環境での無効なデータの入力を防ぐのにも役立ちます。
このガイドでは、データベースの外部キーを使用して参照整合性を適用します。 このガイドはMySQLデータベースでテストされていますが、構文を少し変更するだけで、他のSQLベースのデータベースでも機能します。
前提条件
このチュートリアルを完了するには、次のものが必要です。
-
非ルートで保護されたUbuntu20.04サーバー
sudo
ユーザーと基本的なファイアウォール。 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
次に、 job_titles
を使用したテーブル CREATE TABLE
指図。 このテーブルは、データベースで使用可能なすべての役職のルックアップテーブルとして機能します。 The job_title_id
は、データベース内の各役職を一意に識別する主キーです。 BIGINT
最大に対応できるデータ型 2^63-1
記録。 使用している AUTO_INCREMENT
新しい役職を挿入するたびにMySQLが自動的に連続する数値を割り当てるようにするキーワード。
の中に CREATE TABLE
コマンド、インクルード job_title_name
役職の人間が読める値を格納する列。 この列には、最大長の文字列値が格納されます。 50
文字。 このデータ型を構文で定義します VARCHAR(50)
.
続く CREATE TABLE
コマンド、MySQLに使用するように指示します InnoDB
を含めることによるデータベースエンジン ENGINE = InnoDB
キーワード。 これは、データベースアプリケーションで高い信頼性と高いパフォーマンスを確保しながら、同時実行性を処理するトランザクション対応の汎用ストレージエンジンです。
次のコマンドを実行して、 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
テーブル。 このテーブルには、会社のすべてのスタッフのレコードが保持されます。 The job_title_id
の列 employees
テーブルは、の同じ列を指します job_titles
テーブル。 ステートメントを発行することでこれを達成しています FOREIGN KEY (job_title_id) REFERENCES job_titles (job_title_id)
. 一貫性を保つために、 BIGINT
関連する列に使用したデータ型。
以下では employees
テーブル、 employees_id
それは PRIMARY KEY
そして、あなたは AUTO_INCREMENT
生成された新しいキーワード employees_ids
新しい値を挿入すると。
を使用して従業員の名前をキャプチャしています first_name
と last_name
最大長が 50
文字。 このデータ型は、電話番号にも最適です。 したがって、 VARCHAR(50)
データ型は first_name
, last_name
、 と phone
田畑。
2つの相互リンクされたテーブルからデータを取得するときの速度を向上させるには、次のステートメントを使用します。 INDEX (job_title_id)
インデックスを作成するには job_title_id
桁。 繰り返しになりますが、必ずキーワードを含めてください ENGINE = InnoDB
を利用するには InnoDB
ステップ1で概説されているストレージエンジン。
を作成するには 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 MANAGER
CLERK
LEVEL 1 SUPERVISOR
ここで、いくつかの無効なレコードをに追加してみてください employees
次を実行してテーブル INSERT
ステートメント:
- 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');
上記 INSERT
ステートメントはすべて失敗し、次のエラーが表示されるはずです。 4
, 15
、 と 7
無効です job_title_ids
.
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)
...
ここまでで、参照整合性を実装することは、データを検証し、相互リンクされたテーブルを操作するときに存在しないレコードの入力を防ぐための便利なアプローチであることに気付くでしょう。 また、外部キーを使用することで、相互リンクされたデータを効率的にクエリできる最適化されたデータベースを作成しています。
たとえば、役職名が綴られているすべてのスタッフのレコードを取得するには、次のコマンドを実行します。 JOIN
に対する声明 employees
と job_titles
テーブル。
- 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)
上記の出力でわかるように、1つあります BRANCH MANAGER
、3つ CLERK
、および1つ LEVEL 1 SUPERVISOR
.
リンクされた子テーブルで外部キーによってすでに参照されている親レコードの削除を防ぐ場合にも、外部キーは優れています。 これを適用できる実際の例をいくつか示します。
-
eコマースWebサイトでは、顧客の詳細が誤って削除されるのを防ぐことができます。
customers
で顧客のアクティブな注文を行ったときのテーブルsales
テーブル。 -
図書館システムでは、学生をから削除することを防ぐことができます
registers
学生がレコードを関連付けたときのテーブルissued_books
テーブル。 -
銀行では、外部キーアプローチを使用して、レコードをから削除しないようにすることができます。
savings_accounts
クライアントがすでにいくつかの預金/引き出しを行ったときのテーブルsavings_accounts_transactions
テーブル。
同様に、テーブル内のデータの削除を試みることができます。 コマンドライン端末で、から1つの位置を削除します。 job_titles
テーブル:
- 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;
これで、以下に示すように、使用可能なすべてのポジションのリストが表示されます。 The CEO
役割には job_title_id
の 4
:
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つの行があります。 次に、で新しい役割を削除します job_title_id
の 4
関連するレコードをに入力する前に employees
テーブル。
- DELETE FROM job_titles
- WHERE job_title_id = 4 ;
The DELETE
ステートメントは成功するはずです。
OutputQuery OK, 1 row affected (0.00 sec)
上記のすべてのテストをエラーなしで完了した後、外部キーが期待どおりに機能していることが明らかになりました。
結論
このガイドでは、相互リンクされたテーブルを使用してサンプルデータベースを設定し、リレーショナルデータベース管理システムでの参照整合性の使用を実践しました。 データベースを不整合な状態にするデータの削除を検証および防止する上で、外部キーがいかに重要であるかを見てきました。 このガイドの知識を次のデータベースプロジェクトに使用して、外部キーを活用してください。
MySQLデータベースの詳細については、次のチュートリアルを確認してください。