MySQLで外部キーを設定する方法

MySQLで外部キーを設定する方法

外部キー(Foreign Key)は、データベースのリレーショナル構造を強化するために使用され、テーブル間の関係性を確保する仕組みです。
外部キーを設定することで、参照整合性(referential integrity)を保証し、関連するデータの一貫性を維持できます。

1. 外部キーの役割

外部キーは、あるテーブルのカラム(列)が別のテーブルの主キーやユニークキーを参照することを強制する制約です。
これにより、例えば、orders テーブルの customer_id が customers テーブルの id に一致しなければならない、という関係を持つことができます。
これによって、不正なデータの挿入を防ぎ、データの整合性を保つことができます。

2. 外部キーを設定するための前提条件

外部キーを設定する前に、以下の条件が満たされている必要があります。

  • 参照される側のテーブル(親テーブル)のカラムが主キー(PRIMARY KEY)もしくはユニークキー(UNIQUE KEY)であること。
  • 参照する側のテーブル(子テーブル)のカラムがインデックスを持っていること(外部キー設定時に自動的にインデックスが作成されます)。
  • 両方のテーブルが同じストレージエンジン(通常はInnoDB)を使用していること。

3. 外部キーの設定方法

外部キーは、テーブルを作成する際、または既存のテーブルに後から追加することができます。

3.1. 新しいテーブルを作成する際に外部キーを設定する

新しいテーブルを作成する際に外部キーを設定するには、CREATE TABLE文を使用します。
以下の例では、orders テーブルが customers テーブルの id を参照する外部キーを設定しています。

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_date DATE NOT NULL,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

この例では、orders テーブルの customer_id カラムが customers テーブルの id カラムを参照しています。

3.2. 既存のテーブルに外部キーを追加する

既存のテーブルに外部キーを追加するには、ALTER TABLE文を使用します。
以下にその例は以下の通りです。

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);

ここで、fk_customer は外部キーの名前であり、orders テーブルの customer_id カラムが customers テーブルの id カラムを参照するようにしています。

4. 外部キーの制約オプション

外部キー制約には、データの削除や更新時の動作を指定するためのオプションがあります。
これには以下のものがあります。

  • ON DELETE CASCADE: 親テーブルのデータが削除されたときに、それを参照している子テーブルのデータも自動的に削除されます。
  • ON DELETE SET NULL: 親テーブルのデータが削除されたときに、それを参照している子テーブルのカラムをNULLに設定します。
  • ON UPDATE CASCADE: 親テーブルの主キーが更新されたときに、それを参照している子テーブルの外部キーも自動的に更新されます。

例として、ON DELETE CASCADE を指定する方法を以下に示します。

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_date DATE NOT NULL,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
  ON DELETE CASCADE
);

この設定により、customers テーブルのレコードが削除されると、orders テーブルでその customer_id を参照するレコードも自動的に削除されます。

5. 外部キーの削除方法

外部キー制約を削除するには、ALTER TABLE 文を使用して削除することができます。
以下にその例は以下の通りです。

ALTER TABLE orders
DROP FOREIGN KEY fk_customer;

この文は、orders テーブルから fk_customer という名前の外部キー制約を削除します。

まとめ

外部キーを設定することで、MySQLデータベースのテーブル間の参照整合性を確保し、データの一貫性を維持することができます。
必要に応じて制約オプションを活用することで、データの整合性を強化することができます。