データベース整合性を保つ鍵！MySQL 外部キー制約と参照アクション
MySQLにおける外部キー参照アクションの変更方法
MySQLデータベースにおいて、関連テーブル間のデータ整合性を保つために重要な役割を果たすのが外部キー制約です。外部キー制約には、参照アクションと呼ばれる設定項目があり、親テーブルのデータ変更が子テーブルにどのような影響を与えるかを定義します。
本記事では、MySQLにおける外部キー参照アクションの変更方法について、分かりやすく解説します。
前提知識
本記事の内容を理解するためには、以下の知識が必要です。
- 外部キー制約と参照アクションの役割
- MySQLデータベースの基本的な概念
変更方法
MySQLで外部キー参照アクションを変更するには、ALTER TABLEステートメントを使用します。具体的な構文は以下の通りです。
ALTER TABLE child_table
MODIFY CONSTRAINT foreign_key_name
REFERENCE parent_table
ON DELETE [RESTRICT | CASCADE | SET NULL | NO ACTION]
ON UPDATE [RESTRICT | CASCADE | SET NULL | NO ACTION];
例
以下は、ordersテーブルのorder_id列がproductsテーブルのproduct_id列を参照する外部キー制約 fk_orders_productsの参照アクションをCASCADEに変更する例です。
ALTER TABLE orders
MODIFY CONSTRAINT fk_orders_products
REFERENCE products
ON DELETE CASCADE
ON UPDATE CASCADE;
この変更により、productsテーブルの参照されている行が削除または更新されると、ordersテーブルの子行も自動的に削除または更新されます。
参照アクションの種類
| アクション名 | 説明 |
|---|---|
| RESTRICT | 親テーブルの参照されている行が削除または更新されるのを禁止します。 |
| CASCADE | 親テーブルの参照されている行が削除または更新されると、子行も自動的に削除または更新されます。 |
| SET NULL | 親テーブルの参照されている行が削除または更新されると、子行の外部キー列の値がNULLに設定されます。 |
| NO ACTION | RESTRICTと同じ動作です。 |
注意事項
- 子テーブルで参照されている列を変更する場合は、まず外部キー制約を削除してから、新しい制約を作成する必要があります。
- 参照アクションの変更は、
InnoDBテーブルのみでサポートされます。 - 参照アクションを変更する前に、既存のデータにどのような影響を与えるかを十分に検討する必要があります。
- 現時点の参照アクションは
RESTRICTであり、productsテーブルの参照される行が削除または更新されると、ordersテーブルの子行の削除または更新を禁止する。 ordersテーブルのproduct_id列は、productsテーブルのproduct_id列を参照する外部キー制約を持つ。productsテーブルには、product_id、product_name、priceなどの列がある。ordersテーブルには、order_id、product_id、customer_idなどの列がある。
要件
変更
以下のSQLステートメントを使用して、参照アクションをCASCADEに変更します。
ALTER TABLE orders
MODIFY CONSTRAINT fk_orders_products
REFERENCE products
ON DELETE CASCADE
ON UPDATE CASCADE;
説明
ON UPDATE CASCADE： この句は、親テーブルの参照される行が更新されると、子行も自動的に更新されることを指定します。REFERENCE products： この句は、参照される親テーブルを指定します。この場合、productsテーブルです。MODIFY CONSTRAINT fk_orders_products： この句は、変更する外部キー制約を指定します。fk_orders_productsは、この制約の名前です。ALTER TABLE orders： このステートメントは、ordersテーブルを変更することを示します。
結果
この例では、CASCADEという参照アクションを使用しました。他の参照アクション（RESTRICT、SET NULL、NO ACTION）を使用することもできます。
また、この例では、InnoDBテーブルを使用していることを前提としています。他のストレージエンジンを使用している場合は、サポートされている参照アクションが異なる場合があります。
GUI ツールを使用した外部キー参照アクションの変更
方法
ここでは、MySQL Workbench を例として説明します。
- MySQL Workbench を起動し、該当するデータベースに接続します。
- 変更対象のテーブルをナビゲーションペインで選択します。
- 「テーブル構造」タブをクリックします。
- 「外部キー」テーブルで、変更対象の外部キー制約を選択します。
- 「参照アクション」ドロップダウンリストから、新しい参照アクションを選択します。
- 「適用」ボタンをクリックします。
以下は、MySQL Workbench を使用して、ordersテーブルとproductsテーブル間の外部キー制約の参照アクションを CASCADE に変更する方法です。
- ナビゲーションペインで
ordersテーブルを選択します。 - 「外部キー」テーブルで、
fk_orders_products制約を選択します。
この方法の利点
- GUI ツールを使用することで、SQL ステートメントを記述する必要がなく、視覚的に操作できます。
- 複雑な変更を行う場合は、GUI ツールよりも
ALTER TABLEステートメントを使用する方が柔軟性が高いかもしれません。 - すべての GUI ツールが外部キー参照アクションの変更をサポートしているわけではありません。
mysql foreign-keys foreign-key-relationship
