basics mysql foreign key constraint with examples
このチュートリアルでは、構文、追加、宣言、削除、変更の方法など、MySQL FOREIGNKEY制約の基本を例を挙げて説明します。
非常に簡単に言えば、FOREIGN KEYは、MySQLの2つ以上のテーブルをリンクするために使用されます。
さまざまな時点でさまざまなタイプのデータをクエリおよび更新するには、MySQLテーブルを接続する必要があります。したがって、2つのテーブル間にリンクポイントを設定することが不可欠です。
このチュートリアルでは、外部キーのさまざまな使用法と、それを宣言および変更する方法、およびテーブル構造全体にどのような制約があるかについて説明します。
学習内容：
MySQL FOREIGN KEY
構文：
CONSTRAINT {constraintName} FOREIGN KEY (referringColumnName) REFERENCES {referredTable}({referredColumn}) ON UPDATE {reference-option} ON DELETE {reference-option} 上記は、テーブルの作成中またはテーブルの作成中にテーブルに対してFOREIGNKEYを指定するときに使用される構文です。 ALTERTABLEステートメント。
構文のさまざまなコンポーネントを理解しましょう。
- constrantName： これは、指定されているFK制約に対して定義する記号名です。これがスキップされると、MySQLエンジンはFK制約に名前を自動的に割り当てます。
- referenceColumnName： これは、参照されるテーブルの列で指定されている別のテーブルの値を参照する列です。
- 参照テーブル/親テーブル： これは、値が参照されるテーブルの名前を指します。
- 参照列： 参照されるテーブルの列名。
- 参照オプション： これらは、外部キー制約を保持するテーブルで更新または削除アクションが実行されたときに表示されるアクションです。 UPDATEとDELETEはどちらも、同じ参照オプションまたは異なる参照オプションを持つことができます。
このチュートリアルの後半で、さまざまな参照整合性アクションについて学習します。
Employee / Departmentの例を使用したFOREIGNKEY参照の例を見てみましょう。列DepartmentId（int＆PRIMARY KEY）とdepartmentName（varchar）を持つテーブルDepartmentを作成します。
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL, departmentName VARCHAR(100));以下のような列を持つテーブルEmployeeを作成します。
| カラム | タイプ |
|---|---|
| id | INT（主キー） |
| 名前 | VARCHAR |
| dept_id | 部門テーブルから参照されるINT（外部キー） |
| 住所 | VARCHAR |
| 年齢 | INT |
| ドブ | 日付 |
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT, CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE); ご覧のとおり、上記のEmployeeテーブルでは、Int型のdeptId列を宣言し、departmentId列のDepartmentテーブルからFOREIGNKEYを定義しています。
これが本質的に意味することは、EmployeeテーブルのdeptId列には、Departmentテーブルにある値のみを含めることができるということです。
これらのテーブルにデータを挿入して、FOREIGN KEYCONSTRAINTがどのように機能するかを見てみましょう。
- 最初にDepartmentテーブルにレコードを作成し、Departmentテーブルに追加されたレコードのIDを参照するレコードをEmployeeテーブルに追加します。
INSERT INTO department VALUES (1, 'ENGINEERING') --------- INSERT INTO EMPLOYEE VALUES (1, 'AMIT KUMAR', 'MUMBAI', 32, '1988-02-12',1); 両方のステートメントがエラーなしで実行されることがわかります。
- 次に、存在しないdepartmentIdの値を参照します。
例えば、 以下のクエリステートメントでは、存在しないdepartmentId-10を持つ従業員を作成しています。
INSERT INTO EMPLOYEE VALUES (1, 'DARREN JOHNSON', 'CHICAGO', 32, '1988-02-12',10);- このシナリオでは、次のようなエラーが発生します。
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`my_sql_foreign_key`.`employee`, CONSTRAINT `depIdFk` FOREIGN KEY (`deptId`) REFERENCES `department` (`departmentId`) ON DELETE CASCADE ON UPDATE CASCADE)したがって、大まかに言って、FOREIGN KEY参照を定義するときは、参照されるテーブルに、参照される前にデータが含まれていることを確認することが重要です。
参照整合性アクション
まず、参照整合性とは何かを正確に理解してみましょう。
参照整合性は、FOREIGN KEY関係で相互に関連するテーブルがある場合に、データをクリーンで一貫性のある状態に維持するのに役立ちます。
簡単に言うと、参照整合性とは、外部キーを含む参照テーブルでUPDATEまたはDELETEが発生したときに、データベースエンジンから実行されると予想されるアクションを指します。
例えば、 従業員/部門の例で、DBの特定の行の部門IDを変更するとします。次に、Employeeテーブルのすべての参照行が影響を受けます。このような場合に適用できるさまざまなタイプの参照整合性シナリオを定義できます。
注意： 参照整合性は、FOREIGNKEYのセットアップ/宣言中にONDELETEおよびONUPDATEコマンド/セクションの一部として定義されます。
ここでサンプルクエリを参照してください（従業員/部門の例について）：
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL, departmentName VARCHAR(100)); CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT, CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE {ref-integrity-action} ON DELETE {ref integrity action}); 以下のように、これらのテーブルにいくつかのデータを挿入します。
INSERT INTO department VALUES (1, 'ENGINEERING'), (2,'ACCOUNTING'), (3, 'MARKETING'), (4, 'HR'), (5, 'LEGAL'); INSERT INTO EMPLOYEE VALUES (1, 'AMIT KUMAR', 'MUMBAI', 32,'1988-02-12',1), (2, 'RYAN HILMAN', 'SEATTLE',43, '1977-03-15',1), (3, 'KAVITA SINGH', 'DELHI', 42, '1978-02-18',4), (4, 'DAVID BECKHAM', 'LONDON', 40, '1980-07-13',3), (5, 'PRITI KUMARI', 'DELHI', 35, '1985-12-11',2), (6, 'FRANK BALDING', 'NEW YORK', 35, '1985-08-25',5) MySQLでサポートされている4つの参照アクションがあります。それぞれを理解してみましょう。
＃1）カスケード
これは、最も一般的に使用される参照整合性アクションの1つです。 DELETEとUPDATEをCASCADEに設定すると、参照テーブルの参照テーブルに加えられた変更が適用されます。つまり、従業員/部門の例です。誰かがdepartment_name = ACCOUNTINGと言わなければならないDepartmentテーブルの行を削除すると、Accountingテーブルのdepartment_idを持つEmployeeテーブルのすべての行も削除されます。
例を挙げてこれを理解しましょう。
SELECT * FROM employee;| id | 名前 | 住所 | 年齢 | ドブ | deptId |
|---|---|---|---|---|---|
| 1 | アミット・クマール | ムンバイ | 32 | 1988-02-12 | 1 |
| 二 | ライアンヒルマン | シアトル | 43 | 1977-03-15 | 1 |
| 3 | カヴィタ・シン | デリー | 42 | 1978-02-18 | 4 |
| 4 | デビッド・ベッカム | ロンドン | 40 | 1980-07-13 | 3 |
| 5 | PRITI KUMARI | デリー | 35 | 1985-12-11 | 二 |
| 6 | フランクハゲ | ニューヨーク | 35 | 1985-08-25 | 5 |
departmentName = ’ACCOUNTING’であるDepartmentテーブルからレコードを削除します
DELETE from DEPARTMENT WHERE departmentName='ACCOUNTING';これはCASCADE参照アクションであるため、departmentID = 2（「ACCOUNTING」部門用）のすべての行も削除する必要があります。 Employeeテーブルに対してもう一度SELECTクエリを実行してみましょう。
単体テストと統合テストの例
SELECT * FROM employee;| id | 名前 | 住所 | 年齢 | ドブ | deptId |
|---|---|---|---|---|---|
| 1 | アミット・クマール | ムンバイ | 32 | 1988-02-12 | 1 |
| 二 | ライアンヒルマン | シアトル | 43 | 1977-03-15 | 1 |
| 3 | カヴィタ・シン | デリー | 42 | 1978-02-18 | 4 |
| 4 | デビッド・ベッカム | ロンドン | 40 | 1980-07-13 | 3 |
| 6 | フランクハゲ | ニューヨーク | 35 | 1985-08-25 | 5 |
上記のように、CASCADE参照整合性により、削除された列をFOREIGN KEYとして参照したEmployeeテーブルの行では、それらの行が削除されます。
＃2）制限/アクションなし
RESTRICTまたはNOACTIONモードでは、一部のテーブルでFOREIGNKEYとして参照されている列を持つテーブルでのUPDATEまたはDELETE操作は許可されません。
NO ACTIONモードは、テーブル宣言からONUPDATE句とONDELETE句を省略するだけで適用できます。
同じ例を試してみましょう。この場合は、ONUPDATEおよびONDELETE参照整合性アクションをスキップしてください。
参照テーブルのエントリを削除しようとすると、参照アクションをRESTRICTに設定しているため、エラーが発生します。
DELETE FROM department WHERE departmentName='ACCOUNTING';上記のDELETEコマンドを実行しようとすると、次のようなエラーが表示されます。
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`my_sql_foreign_key`.`employee`, CONSTRAINT `depIdFk` FOREIGN KEY (`deptId`) REFERENCES `department` (`departmentId`))＃3）SET NULL
SET NULLを使用すると、参照されるテーブルでUPDATEまたはDELETEを実行すると、参照するテーブルでFOREIGNKEYとしてマークされている列値に対してNULL値が更新されます。
この参照整合性アクションを使用すると、Employeeテーブルの定義は次のようになります。
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT, CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON DELETE SET NULL); 以下に示すように、参照されるテーブルの行を削除します。
DELETE FROM department WHERE departmentName='ACCOUNTING';この場合、Employeeテーブルの参照値はNULLに設定されます。 EmployeeテーブルでSELECTクエリを実行して、結果を確認します。
SELECT * FROM employee;| id | 名前 | 住所 | 年齢 | ドブ | deptId |
|---|---|---|---|---|---|
| 1 | アミット・クマール | ムンバイ | 32 | 1988-02-12 | 1 |
| 二 | ライアンヒルマン | シアトル | 43 | 1977-03-15 | 1 |
| 3 | カヴィタ・シン | デリー | 42 | 1978-02-18 | 4 |
| 4 | デビッド・ベッカム | ロンドン | 40 | 1980-07-13 | 3 |
| 5 | PRITI KUMARI | デリー | 35 | 1985-12-11 | ヌル |
| 6 | フランクハゲ | ニューヨーク | 35 | 1985-08-25 | 5 |
＃4）デフォルトに設定
SET DEFAULTモードを指定すると、参照されているテーブル内のDELETESが発生した場合に、（列の宣言時に指定された）列のデフォルト値が置き換えられます。
注意 - によると MySQLドキュメント 、SETDEFAULTオプションはMySQLParserでサポートされていますが、InnoDBのようなDBエンジンではサポートされていません。これは将来サポートされる可能性があります。
ただし、このような動作をサポートするために、SET NULLの使用を検討し、デフォルト値を設定できるトリガーをテーブルに定義できます。
ALTERTABLEステートメントを使用してFOREIGNKEY制約を追加する
多くの場合、FOREIGNKEY制約を持たない既存のテーブルにFOREIGNKEY制約を追加したい場合があります。
Employee and Departmentの例で、FOREIGN KEY制約のないemployeeテーブルを作成し、後で制約を導入するとします。これは、ALTERTABLEコマンドを使用して実行できます。
例を挙げてこれを理解してみましょう。
以下のCREATEコマンドの定義を持つEmployeeテーブルがあるとします。
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT);ここでは、列deptIdがありますが、FOREIGNKEY制約はありません。この場合、Departmentテーブルがなくても、レコードを挿入するときに任意の値を指定できます。
ここで、後で別のDepartmentテーブルがあり、そこにFOREIGNKEYとしてdepartmentIdをEmployeeテーブルにリンクするとします。
ALTER TABLE employee ADD CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE; このテーブルに既存のデータがある場合はどうなりますか？テーブルをALTERしてFOREIGNKEY制約を追加できますか？
答えは「はい」です。別のテーブルから参照される列の既存の値が、親テーブル自体に存在する値を持つ必要があるという条件で可能です。
FOREIGN KEY制約なしでEmployeeテーブルを作成し、データを追加して、ALTERコマンドを使用してFOREIGNKEY制約を追加してみてください。
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT); INSERT INTO EMPLOYEE VALUES (1, 'DARREN JOHNSON', 'CHICAGO', 32, '1988-02-12',1); INSERT INTO EMPLOYEE VALUES (2, 'ANITA SHERWIN', 'COLUMBIA', 32, '1988-02-12',10); 以下に示すように、Departmentテーブルを作成し、Employeeテーブルの「deptId」フィールドに対してFOREIGNKEYを追加します。
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL DEFAULT 1000, departmentName VARCHAR(100));この時点で、FOREIGN KEY制約を追加しようとすると、
ALTER TABLE employee ADD CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE; 次に、Employeeテーブルにいくつかのデータが含まれているが、Departmentテーブルにはまだデータがないため、参照整合性の制約を満たすことができないため、エラーが発生します。
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`my_sql_foreign_key`.`#sql-63_87`, CONSTRAINT `depIdFk` FOREIGN KEY (`deptId`) REFERENCES `department` (`departmentId`) ON DELETE CASCADE ON UPDATE CASCADE)FOREIGN KEY制約を設定するには、最初にDepartmentテーブルにデータを追加する必要があります。必要なレコードをDepartmentテーブルに挿入しましょう。
INSERT INTO department VALUES (1, 'ENGINEERING'),(10,'ACCOUNTING');同じALTERTABLEステートメントを実行して、FOREIGNKEY制約を再度追加します。今回は、コマンドが成功し、Employeeテーブルが正常に更新されてDeptIdがDepartmentテーブルのFOREIGNKEYになっていることがわかります。
外部キー制約の削除
FOREIGN KEY制約を追加するのと同様に、既存のFOREIGNKEY制約をテーブルから削除/削除することもできます。
これは、ALTERTABLEコマンドを使用して実行できます。
構文：
ALTER TABLE {childTable} DROP FOREIGN KEY {foreign key constraint name}; ここで、「childTable」はFOREIGN KEY制約が定義されているテーブルの名前であり、「foreignkeyconstraintname」はFOREIGNKEYを定義するために使用された名前/記号です。
Employee / Departmentテーブルを使用した例を見てみましょう。 Employeeテーブルから「depIdFk」という名前の制約を削除するには、次のコマンドを使用します。
ALTER TABLE employee DROP FOREIGN KEY depIdFk;よくある質問
Q＃1）MySQLで外部キーを変更するにはどうすればよいですか？
回答： FOREGIN KEYは、ALTERTABLEコマンドを使用して追加/削除できます。
新しいFOREIGNKEYを変更または追加するには、ALTERコマンドを使用して、子テーブルから参照されるFOREIGNKEYと参照テーブルの列を定義します。
Q＃2）MySQLで複数の外部キーを設定するにはどうすればよいですか？
回答： MySQLのテーブルには、同じ親テーブルまたは異なる親テーブルに依存する可能性のある複数の外部キーを含めることができます。
Employee / Departmentテーブルを使用して、Department名のFOREIGNKEYとEmployeeテーブルのDepartmentIdを追加しましょう。
以下のように、両方のテーブルのCREATEステートメントを参照してください。
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL DEFAULT 1000, departmentName VARCHAR(100) UNIQUE NOT NULL); ----xxxxx------xxxxx------xxxxx-------xxxxx------xxxxx CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT, depName VARCHAR(100), CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT depNameFk FOREIGN KEY (depName) REFERENCES department(departmentName) ON UPDATE CASCADE ON DELETE CASCADE); Q＃3）MySQLで外部キー制約を無効にする方法は？
回答： 通常、外部キー制約は、参照されている既存のテーブルを誰かが切り捨てようとしているときに必要になります。これを行うには、次のコマンドを使用できます。
SET FOREIGN_KEY_CHECKS=0;これにより、セッション変数が設定され、FOREIGN_KEY_CHECKSが一時的に無効になります。この設定の後、先に進んで削除/切り捨てを実行できます。これは、他の方法では不可能でした。
ただし、これが管理者権限であり、慎重に使用する必要があることを確認してください。
Q＃4）MySQLでテーブルの外部キー参照を見つけるにはどうすればよいですか？
回答： 存在するすべてのFOREIGNKEY制約を一覧表示するには、「INFORMATION_SCHEMA」の「INNODB_FOREIGN_COLS」テーブルを使用できます。
以下のコマンドを実行するだけで、特定のMySQLサーバーインスタンスに存在するすべてのFOREIGNKEY宣言を取得できます。
| ID | FOR_COL_NAME | REF_COL_NAME | POS |
|---|---|---|---|
| my_sql_foreign_key / depIdFk | deptId | departmentId | 1 |
Q＃5）FOREIGN KEYとして参照される列は、参照されるテーブルの主キーである必要がありますか？
回答： FOREIGN KEYの定義により、FOREIGN KEYとして参照されている列は、参照されているテーブルのPRIMARYKEYである必要があります。
ただし、MySQLの新しいバージョンとInnoDBデータベースエンジンでは、UNIQUE制約があり、必ずしもPRIMARYKEYであるとは限らないFOREIGNKEYを持つ列を参照することもできます。
Q＃6）FOREIGN KEYはMySQLでINDEXを作成しますか？
回答： 主キーと一意性制約の両方について、MySQLはそのような列のINDEXを自動的に作成します。
FOREIGN KEY参照は、主キーである列または一意の値を持つ列のいずれかにのみ適用できることがすでにわかっているため、FOREIGN KEYと呼ばれるすべての列には、それらに対して作成されたインデックスがあります。
テーブルのインデックスを表示するには、次のコマンドを使用します。
SHOW INDEX from {dbName.tableName};したがって、Employee / Departmentの例では、DepartmentテーブルのFOREIGNKEYとしてEmployeeにdeptIdを追加しました。
EmployeeテーブルとDepartmentテーブルに作成されたインデックスを見てみましょう。
USE my_sql_foreign_key; SHOW INDEX from employee; | テーブル | Non_unique | Key_name | Seq_in_index | Column_name | 照合 | カーディナリティ | Sub_part | パック | ヌル | Index_type |
|---|---|---|---|---|---|---|---|---|---|---|
| 社員 | 0 | プライマリ | 1 | id | に | 0 | ヌル | ヌル | BTREE | |
| 社員 | 1 | depIdFk | 1 | deptId | に | 0 | ヌル | ヌル | はい | BTREE |
2つのインデックスを確認できます。1つはEmployeeテーブルの主キーで、もう1つはDepartmentテーブルから参照されるFOREIGN KEYdepId用です。
SHOW INDEX from department;| テーブル | Non_unique | Key_name | Seq_in_index | Column_name | 照合 | カーディナリティ | Sub_part | パック | ヌル | Index_type |
|---|---|---|---|---|---|---|---|---|---|---|
| 部門 | 0 | プライマリ | 1 | departmentId | に | 0 | ヌル | ヌル | BTREE |
ここでは、Departmentテーブルの場合、主キー（EmployeeテーブルではFOREIGN KEYとして参照されている）のインデックスが1つしかないことがわかります。
Q＃7）MySQLでFOREIGN KEYをNULLにすることはできますか？
回答： はい、別のテーブルにFOREIGNKEY依存関係がある列にNULLを設定してもまったく問題ありません。これは、NULLが実際の値ではないため、親テーブルの値と照合/比較されないという事実も暗示しています。
結論
このチュートリアルでは、MySQLデータベースでの外部キーの使用に関連するさまざまな概念について学習しました。
FOREIGN KEYは、適切な制限を使用して更新と削除を容易にしますが、そのような関係が多数あると、挿入および/または削除のプロセス全体が非常に面倒になる場合があります。
面接の質問をテストする安らかなWebサービス
FOREIGN KEYSを作成する方法と、既存のFOREIGNKEYを更新して子テーブルから削除する方法を学びました。また、さまざまな参照整合性アクションと、CASCADE、NO ACTION、SETNULLなどのさまざまな使用可能なオプションを使用してさまざまな動作を実現する方法についても学びました。