MySQL 外部キー制約 (FOREIGN KEY) ケースの説明

MySQL 外部キー制約 (FOREIGN KEY) はテーブルの特別なフィールドであり、主キー制約とともによく使用されます。関連付けられているリレーションシップを持つ 2 つのテーブルの場合、関連付けられているフィールドの主キーが配置されているテーブルがプライマリ テーブル (親テーブル) であり、外部キーが配置されているテーブルがセカンダリ テーブル (子テーブル) です。

外部キーは、プライマリ テーブルとセカンダリ テーブル間の関連付けを確立し、2 つのテーブルのデータを接続して、2 つのテーブルのデータの一貫性と整合性を制限するために使用されます。たとえば、果物屋にはリンゴ、桃、プラム、スイカの 4 種類の果物しかありません。果物屋に果物を買いに行くと、リンゴ、桃、プラム、スイカしか選べません。他の果物は購入できません。

メイン テーブルからレコードが削除された場合、テーブル内の対応するレコードもそれに応じて変更する必要があります。テーブルには 1 つ以上の外部キーを含めることができ、外部キーは NULL 値にすることができます。外部キーが NULL 値でない場合、各外部キーの値はメイン テーブルの主キーの値と等しくなければなりません。

外部キーを定義するときは、次の規則に従う必要があります。

• プライマリ テーブルは、データベース内に既に存在しているか、現在作成中のテーブルである必要があります。後者の場合、マスターテーブルとスレーブテーブルは同じテーブルです。このようなテーブルは自己参照テーブルと呼ばれ、この構造は自己参照整合性と呼ばれます。
• 主テーブルには主キーを定義する必要があります。
• 主キーには null 値を含めることはできませんが、外部キーには null 値が許可されます。つまり、外部キーの null 以外の値がすべて指定された主キーに表示されている限り、この外部キーの内容は正しいことになります。
• メイン テーブルのテーブル名の後に列名または列名の組み合わせを指定します。この列または列の組み合わせは、主テーブルの主キーまたは候補キーである必要があります。
• 外部キーの列数は、主テーブルの主キーの列数と同じである必要があります。
• 外部キー列のデータ型は、主テーブルの主キー内の対応する列のデータ型と同じである必要があります。

テーブル作成時に外部キー制約を設定する

CREATE TABLE ステートメントでは、外部キーは FOREIGN KEY キーワードによって指定されます。具体的な構文形式は次のとおりです。

[CONSTRAINT <外部キー名>] FOREIGN KEY フィールド名 [, フィールド名 2, ...]
参照 <主テーブル名> 主キー列 1 [、主キー列 2、…]

例1

この例では、テーブル間の外部キー関係を示すために、test_db データベースに部門テーブル tb_dept1 を作成します。テーブル構造を次の表に示します。

tb_dept1 を作成するための SQL 文と実行結果は次のとおりです。

mysql> テーブル tb_dept1 を作成します
    -> (
    -> id INT(11) 主キー、
    -> 名前 VARCHAR(22) NOT NULL、
    -> 場所 VARCHAR(50)
    -> );
クエリは正常、影響を受けた行は 0 行 (0.37 秒)

データ テーブル tb_emp6 を作成し、テーブル tb_emp6 に外部キー制約を作成します。そのキー deptId を外部キーとしてテーブル tb_dept1 の主キー id に関連付けます。SQL ステートメントと実行結果は次のとおりです。

mysql> テーブル tb_emp6 を作成します
    -> (
    -> id INT(11) 主キー、
    -> 名前 VARCHAR(25)、
    -> 部門ID INT(11)、
    -> 給与FLOAT、
    -> 制約 fk_emp_dept1
    -> 外部キー(deptId) 参照 tb_dept1(id)
    -> );
クエリは正常、影響を受けた行は 0 行 (0.37 秒)

mysql> DESC tb_emp6;
+--------+-------------+------+------+--------+-------+
| フィールド | タイプ | Null | キー | デフォルト | 追加 |
+--------+-------------+------+------+--------+-------+
| id | int(11) | NO | PRI | NULL | |
| 名前 | varchar(25) | はい | | NULL | |
| deptId | int(11) | はい | MUL | NULL | |
| 給与 | 浮動小数点 | YES | | NULL | |
+--------+-------------+------+------+--------+-------+
セット4列（1.33秒）

上記のステートメントが正常に実行されると、fk_emp_dept1 という名前の外部キー制約がテーブル tb_emp6 に追加されます。外部キー名は deptId で、テーブル tb_dept1 の主キー ID に依存します。

注意: スレーブ テーブルの外部キーはマスター テーブルの主キーに関連付けられている必要があり、主キーと外部キーのデータ型は一貫している必要があります。たとえば、両方とも INT 型であるか、両方とも CHAR 型です。この要件が満たされていない場合、スレーブ テーブルの作成時に「ERROR 1005(HY000): テーブルを作成できません」というエラーが発生します。

テーブルを変更するときに外部キー制約を追加する

テーブルを変更するときに外部キー制約を追加することもできますが、外部キー制約を追加するための前提条件は、スレーブ テーブルの外部キー列のデータがマスター テーブルの主キー列のデータと一致しているか、データが存在しないことです。

データ テーブルを変更するときに外部キー制約を追加するための構文は次のとおりです。

ALTER TABLE <テーブル名> ADD CONSTRAINT <外部キー名>
FOREIGN KEY(<列名>) REFERENCES <主テーブル名> (<列名>);

例2

データ テーブル tb_emp2 を変更し、フィールド deptId を外部キーとして設定し、データ テーブル tb_dept1 の主キー id に関連付けます。SQL ステートメントと実行結果は次のとおりです。

mysql> テーブル tb_emp2 を変更します
    -> 制約 fk_tb_dept1 を追加
    -> 外部キー(部門ID)
    -> 参照 tb_dept1(id);
クエリは正常、影響を受けた行は 0 行 (1.38 秒)
レコード: 0 重複: 0 警告: 0

mysql> SHOW CREATE TABLE tb_emp2\G
************************** 1. 行 ****************************
       テーブル: tb_emp2
テーブルの作成: CREATE TABLE `tb_emp2` (
  `id` int(11) NULLではない、
  `name` varchar(30) デフォルト NULL,
  `deptId` int(11) デフォルト NULL,
  `salary` float デフォルト NULL、
  主キー (`id`)、
  キー `fk_tb_dept1` (`deptId`)、
  制約 `fk_tb_dept1` 外部キー (`deptId`) 参照 `tb_dept1` (`id`)
) エンジン=InnoDB デフォルト文字セット=gb2312
セット内1列（0.12秒）

注意: すでに作成されているデータ テーブルに外部キー制約を追加する場合は、外部キー制約が追加される列の値がすべて主キー列からのものであり、外部キー列が空であってはならないことを確認してください。

外部キー制約を削除する

テーブル内で外部キー制約が不要になった場合は、テーブルから削除する必要があります。外部キーが削除されると、プライマリ テーブルとセカンダリ テーブル間の関連付けが削除されます。

外部キー制約を削除するための構文は次のとおりです。

ALTER TABLE <テーブル名> DROP FOREIGN KEY <外部キー制約名>;

例3

データテーブルtb_emp2の外部キー制約fk_tb_dept1を削除します。SQL文と実行結果は次のとおりです。

mysql> テーブル tb_emp2 を変更します
    -> 外部キー f​​k_tb_dept1 を削除します。
クエリは正常、影響を受けた行は 0 行 (0.19 秒)
レコード: 0 重複: 0 警告: 0

mysql> SHOW CREATE TABLE tb_emp2\G
************************** 1. 行 ****************************
       テーブル: tb_emp2
テーブルの作成: CREATE TABLE `tb_emp2` (
  `id` int(11) NULLではない、
  `name` varchar(30) デフォルト NULL,
  `deptId` int(11) デフォルト NULL,
  `salary` float デフォルト NULL、
  主キー (`id`)、
  キー `fk_tb_dept1` (`deptId`)
) エンジン=InnoDB デフォルト文字セット=gb2312
セット内の 1 行 (0.00 秒)

tb_emp2 には FOREIGN KEY が存在しなくなり、fk_emp_dept という名前の元の外部キー制約が正常に削除されたことがわかります。

これで、MySQL 外部キー制約 (FOREIGN KEY) のケーススタディに関するこの記事は終了です。MySQL 外部キー制約 (FOREIGN KEY) に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。