SQLで外部キーを使用する方法

投稿日: 2022-04-20 2022-10-09
タグ: Databases, Development, mysql, sql, System Tools, ubuntu

著者は、 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データベースの詳細については、次のチュートリアルを確認してください。

getdocs

13036