MySQL 外部キー制約の例の説明

MySQL の外部キー制約は、2 つのテーブル間のリンクを確立するために使用されます。 1 つのテーブルが変更されると、もう 1 つのテーブルも変更されます。この機能の主な目的は、テーブルデータの一貫性と整合性を確保することです。
外部キーによって関連付けられた 2 つのテーブルの場合、関連フィールドの主キーが配置されているテーブルは主テーブル (親テーブルとも呼ばれます) であり、外部キーが配置されているテーブルは副テーブル (子テーブルとも呼ばれます) です。外部キーを定義するときは、いくつかのルールに従う必要があります。

1. 親テーブルはデータベース内に既に存在しているか、現在作成中のテーブルである必要があります。後者の場合、親テーブルと子テーブルは同じテーブルです。このようなテーブルは自己参照テーブルと呼ばれ、この構造は自己参照と呼ばれます。
2. 親テーブルに主キーを定義する必要があります。
3. 主キーには null 値を含めることはできませんが、外部キーには null 値を含めることができます。つまり、外部キーの null 以外の値がすべて指定された主キーに表示されている限り、この外部キーの内容は正しいことになります。
4. 外部キーの列数は、親テーブルの主キーの列数と同じである必要があります。
5. 外部キーの列のデータ型は、親テーブルの主キーの対応する列のデータ型と同じである必要があります。これらはすべてかなり一般的なので、いくつか例を見てみましょう。

mysql:yeyztest ::>>テーブルfk_test_1(を作成します 
 -> id int not null 主キー auto_increment,
 -> name varchar() デフォルト '');
クエリは正常、行は影響を受けました (0.10 秒)

mysql:yeyztest ::>>テーブルfk_test_2(を作成します
 -> id int not null 主キー auto_increment,
 -> uid int、 
 -> 外部キー fk_uid(uid) は fk_test_1(id) を参照します。
クエリは正常、行は影響を受けました (0.06 秒)

ここでは、fk_test_1 と fk_test_2 の 2 つのテーブルを作成します。fk_test_2 の uid 列に外部キーを設定し、fk_test_1 のテーブルの id 列を関連付けます。ここでは、fk_test_1 が親テーブルで、fk_test_2 が子テーブルであることは明らかです。次に、データ挿入実験を行います。

mysql:yeyztest ::>>fk_test_1 に値 (,'aaa'),(,'bbb') を挿入します。
クエリは正常、行は影響を受けました (0.00 秒)
レコード: 重複: 警告: 

mysql:yeyztest ::>>fk_test_1 から * を選択します。
+----+------+
| ID | 名前 |
+----+------+
| | ああ |
| | bbb |
+----+------+
 セット内の行数 (0.00 秒)

mysql:yeyztest ::>>fk_test_2 に値 (,),(,); を挿入します。
クエリは正常、行は影響を受けました (0.00 秒)
レコード: 重複: 警告: 

mysql:yeyztest ::>>fk_test_2 の値に挿入します（,）;
クエリは正常、行は影響を受けました (0.00 秒)


mysql:yeyztest ::>>fk_test_2 の値に挿入します（,）;  
エラー (): 子行を追加または更新できません: 外部キー制約が失敗しました (`yeyztest`.`fk_test_2`、制約 `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`))

まず、メインテーブルに id=1 と id=2 の 2 つのデータを挿入し、次に子テーブルにデータを挿入します。子テーブルは、uid=1 と uid=2 のデータを正常に挿入できますが、uid=3 のデータの挿入は失敗します。つまり、デフォルトでは、子テーブルに挿入する場合、挿入される外部キー関連フィールド値は、親テーブルの関連列に含まれる値である必要があります。ここでのデフォルトの状況に注意してください。これについては後で説明します。

削除状況を見てみましょう。

mysql:yeyztest ::>>fk_test_2 から * を選択します。
+----+------+
| ID | ユーザID |
+----+------+
| | |
| | |
| | |
+----+------+
 セット内の行数 (0.00 秒)

mysql:yeyztest ::>>id=; の fk_test_2 から削除します。
クエリは正常、行は影響を受けました (0.00 秒)

mysql:yeyztest ::>>fk_test_1 から * を選択します。  
+----+------+
| ID | 名前 |
+----+------+
| | ああ |
| | bbb |
+----+------+
 セット内の行数 (0.00 秒)

mysql:yeyztest ::>>id=; の fk_test_1 から削除します。 
エラー (): 親行を削除または更新できません: 外部キー制約が失敗しました (`yeyztest`.`fk_test_2`、制約 `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`))

子テーブル fk_test_2 で削除しても問題ないことがわかりますが、親テーブル fk_test_1 で削除すると、id=1 の値が削除できないことがわかります。理由は、外部キー制約があるためです。つまり、デフォルトでは、親テーブルから削除する場合、子テーブルですでに従属関係にある列値を直接削除することはできません。ここではデフォルトに注意してください。これについては以下で説明します。

削除に失敗したので、更新を試みてください。

mysql:yeyztest ::>>fk_test_1 を更新し、id= を設定します。id=;   
エラー (): 親行を削除または更新できません: 
外部キー制約が失敗する (`yeyztest`.`fk_test_2`、 
制約 `fk_test_2_ibfk_1` 外部キー (`uid`) 参照 `fk_test_1` (`id`))

mysql:yeyztest ::>>fk_test_1 を更新し、name='ccc' を設定します (id=;) 
クエリは正常、行は影響を受けました (0.00 秒)
一致した行: 変更: 警告:

親テーブルの主キー列の更新はまだ正常に実行できないことがわかりますが、他の列の更新は正常に実行できます。

この時点で、外部キーの存在はデータの整合性と統一性を保証するためのものであることはすでにわかっていますが、小さな問題も発生します。つまり、子テーブルに依存する親テーブルの列は削除できません。これは私たちが望んでいることではありません。一部のデータは確かに期限切れになり、削除する必要がありますが、この時点で何をすべきでしょうか。

上記のテストでは、繰り返し言及しましたが、デフォルトでは、外部キーの削除および更新ルールを設定していませんでした。ここで、MySQL は、最も厳しいルールである制限を使用するのに役立ちました。実際には、他にもいくつかのルールがあり、それらはすべてここにリストされています。

親テーブルを削除します。

カスケード、null 設定、アクションなし、制限

親テーブルを更新します。

カスケード、null 設定、アクションなし、制限

で

制限はデフォルトの操作であり、親テーブルは子テーブルが依存する外部キー列を削除または変更できないことを意味します。これは最も安全な設定です。
カスケードとは、親テーブルが削除されると、子テーブルのレコードも直接削除されることを意味します。これは最も危険な設定です。
null を設定すると、親テーブルが削除されたときに、子テーブルは null 値で処理されます。
アクションなしとは、親テーブルが削除されても、子テーブルには変更が加えられないことを意味します。

関連付けを設定するための構文は次のとおりです。

テーブル名を変更し、制約 FK_ID 外部キー (外部キー フィールド名) を追加して、外部テーブル名 (主キー フィールド名) を参照します。
[削除時に {カスケード | null を設定 | アクションなし | 制限}]
[更新時に {カスケード | null を設定 | アクションなし | 制限}]

それでは、カスケードケースから始めて、他の 3 つのケースをテストしてみましょう。

mysql:yeyztest ::>>fk_test_1 から * を選択します。
+----+------+
| ID | 名前 |
+----+------+
| | ccc |
| | bbb |
+----+------+
 セット内の行数 (0.00 秒)

mysql:yeyztest ::>>fk_test_2 から * を選択します。
+----+------+
| ID | ユーザID |
+----+------+
| | |
| | |
+----+------+
 セット内の行数 (0.00 秒)

mysql:yeyztest ::>>テーブル fk_test_2\G の作成を表示
************************** 1. 行 ****************************
  テーブル: fk_test_2
テーブルの作成: CREATE TABLE `fk_test_2` (
 `id` int() NOT NULL AUTO_INCREMENT、
 `uid` int() デフォルト NULL,
 主キー (`id`)、
 キー `fk_uid` (`uid`),
 制約 `fk_test_2_ibfk_1` 外部キー (`uid`) 参照 `fk_test_1` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT= デフォルト CHARSET=utf8
 セット内の行数 (0.00 秒)

mysql:yeyztest ::>>テーブルfk_test_2を変更し、外部キーfk_test_2_ibfk_1を削除します。
クエリは正常、行は影響を受けました (0.02 秒)
レコード: 重複: 警告: 

mysql:yeyztest ::>>テーブルfk_test_2を変更し、制約fk_uidを追加し、外部キー(uid)は削除カスケードでfk_test_1(id)を参照します。
クエリは正常、行は影響を受けました (0.03 秒)
レコード: 重複: 警告: 

#######################################
####ここで親テーブル id= のレコードを削除し、子テーブルの結果を確認します###
#######################################
mysql:yeyztest ::>>id=; の fk_test_1 から削除します。
クエリは正常、行は影響を受けました (0.00 秒)

mysql:yeyztest ::>>fk_test_1 から * を選択します。
+----+------+
| ID | 名前 |
+----+------+
| | ccc |
+----+------+
 セット内の行数 (0.00 秒)

mysql:yeyztest ::>>fk_test_2 から * を選択します。
+----+------+
| ID | ユーザID |
+----+------+
| | |
+----+------+
 セット内の行数 (0.00 秒)

最初は親テーブルの値にid=1とid=2の値が含まれ、子テーブルの値にuid=2とuid=1の値が含まれていることがわかります。親テーブルのid=2の値を削除すると、子テーブルのuid=2の値も直接削除されます。これがカスケードの役割、つまりカスケード削除です。

set null の場合を見てみましょう。

mysql:yeyztest ::>>テーブルfk_test_2を変更し、外部キーfk_uidを削除します。   
クエリは正常、行は影響を受けました (0.02 秒)
レコード: 重複: 警告: 

mysql:yeyztest ::>>テーブル fk_test_2 を変更し、制約 `fk_uid` 外部キー (`uid`) 参照 `fk_test_1` (`id`) を追加し、削除時に null を設定します。
クエリは正常、行は影響を受けました (0.03 秒)
レコード: 重複: 警告: 

mysql:yeyztest ::>>id=; の fk_test_1 から削除します。
クエリは正常、行は影響を受けました (0.00 秒)

mysql:yeyztest ::>>fk_test_1から*を選択します。
空のセット (0.00 秒)

mysql:yeyztest ::>>fk_test_2から*を選択します。
+----+------+
| ID | ユーザID |
+----+------+
| | NULL |
+----+------+
 セット内の行数 (0.00 秒)

set null を設定した後、親テーブルで id=1 の値を削除すると、子テーブルの uid の値が null になり、レコードが削除されないことがわかります。

アクションなしの状況も同様ですが、子テーブル内のレコードは変更されません。

上記は親テーブルを削除する操作です。親テーブルが更新されると、子テーブルも上記の 4 つの状況を選択できますが、基本的には削除と同じなので、ここでは繰り返しません。ご興味があれば、ぜひご自身でお試しください。

最後に、子テーブルの外部キー列には null 値が含まれる可能性があることに注意してください。

mysql:yeyztest ::>>fk_test_1 の値に挿入します（,）;
クエリは正常、行は影響を受けました (0.00 秒)

mysql:yeyztest ::>>fk_test_2から*を選択します。   
+----+------+
| ID | ユーザID |
+----+------+
| | NULL |
+----+------+
 セット内の行数 (0.00 秒)

mysql:yeyztest ::>>fk_test_2 に値を挿入します (,NULL);
クエリは正常、行は影響を受けました (0.00 秒)

mysql:yeyztest ::>>fk_test_2 に値を挿入します (,NULL);
クエリは正常、行は影響を受けました (0.00 秒)

mysql:yeyztest ::>>fk_test_2 から * を選択します。
+----+------+
| ID | ユーザID |
+----+------+
| | NULL |
| | NULL |
| | NULL |
+----+------+
 セット内の行数 (0.00 秒)

上記はMySQL外部キー制約の例の説明の詳細な内容です。MySQL外部キー制約の詳細については、123WORDPRESS.COMの他の関連記事に注目してください。

以下もご興味があるかもしれません: