MariaDB 外国キー(Foreign Key)とは？初心者向け徹底解説【図解あり】

外部キー（がいぶキー）は、リレーショナルデータベースにおける重要な概念の一つで、異なるテーブル間の関連性（リレーションシップ）を定義し、データの整合性を保つために使用されます。

具体的には、あるテーブルのカラム（またはカラムの組み合わせ）が、別のテーブルの主キー（primary key）またはユニークキー（unique key）を参照する制約のことです。

外部キーの役割と利点

• 複雑なクエリの効率化
  外部キーによってテーブル間の関連性が定義されているため、結合（JOIN）操作などを効率的に行うことができます。
• リレーションシップの明確化
  データベースの設計意図を明確にし、テーブル間の関係性を視覚的に理解しやすくします。これにより、データベースの保守や拡張が容易になります。
• データの整合性の向上
  関連するデータ間で矛盾が生じる可能性を低減します。例えば、「顧客」テーブルから特定の顧客を削除しようとした場合に、その顧客を参照している「注文」が存在すれば、削除を制限したり、関連する「注文」も自動的に削除（設定による）したりすることができます。
• 参照整合性の維持
  外部キー制約を設定することで、参照先のテーブルに存在しない値が外部キーとして登録されるのを防ぎます。例えば、「注文」テーブルの「顧客ID」が「顧客」テーブルに存在する顧客のIDのみを参照するように設定できます。これにより、無効な顧客への注文が誤って登録されるのを防ぎます。

MariaDBにおける外部キーの定義方法

外部キーは、テーブル作成時（CREATE TABLEステートメント）またはテーブル変更時（ALTER TABLEステートメント）に定義できます。基本的な構文は以下の通りです。

テーブル作成時

CREATE TABLE 親テーブル (
    親キーカラム データ型 PRIMARY KEY,
    ...
);

CREATE TABLE 子テーブル (
    子キーカラム データ型,
    外部キーカラム データ型,
    ...
    CONSTRAINT 外部キー名
    FOREIGN KEY (外部キーカラム)
    REFERENCES 親テーブル (親キーカラム)
    ON DELETE アクション
    ON UPDATE アクション
);

テーブル変更時

ALTER TABLE 子テーブル
ADD CONSTRAINT 外部キー名
FOREIGN KEY (外部キーカラム)
REFERENCES 親テーブル (親キーカラム)
ON DELETE アクション
ON UPDATE アクション;

各要素の説明

• ON UPDATE アクション
  親テーブルの主キー（またはユニークキー）の値が更新された場合の動作を指定します。主なオプションは ON DELETE と同様です。
• ON DELETE アクション
  親テーブルの行が削除された場合の動作を指定します。主なオプションは以下の通りです。
  • CASCADE
    親テーブルの行が削除された場合、子テーブルの関連する行も自動的に削除されます。
  • SET NULL
    親テーブルの行が削除された場合、子テーブルの外部キーカラムの値を NULL に設定します（外部キーカラムが NULL を許容する場合）。
  • RESTRICT または NO ACTION
    親テーブルの行が削除されようとした場合、子テーブルに参照する行が存在すればエラーが発生し、削除は行われません。
• REFERENCES 親テーブル (親キーカラム)
  参照先の親テーブルとその主キー（またはユニークキー）カラムを指定します。
• FOREIGN KEY (外部キーカラム)
  子テーブル内で外部キーとして使用するカラムを指定します。
• CONSTRAINT 外部キー名
  外部キー制約に付ける名前です。省略可能ですが、後で制約を管理する際に便利です。

例

以下に簡単な例を示します。「顧客」テーブルと「注文」テーブルがあり、「注文」テーブルの 顧客ID が「顧客」テーブルの ID を参照する外部キーとして設定されているとします。

-- 親テーブル：顧客
CREATE TABLE 顧客 (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    名前 VARCHAR(255) NOT NULL
);

-- 子テーブル：注文
CREATE TABLE 注文 (
    注文番号 INT PRIMARY KEY AUTO_INCREMENT,
    顧客ID INT NOT NULL,
    注文日 DATE NOT NULL,
    商品名 VARCHAR(255) NOT NULL,
    CONSTRAINT fk_顧客ID
    FOREIGN KEY (顧客ID)
    REFERENCES 顧客 (ID)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

この例では、「注文」テーブルの 顧客ID カラムが「顧客」テーブルの ID カラムを参照する外部キーとして定義されています。

• ON UPDATE CASCADE: 「顧客」テーブルの顧客の ID が変更された場合、「注文」テーブルの対応する 顧客ID も自動的に更新されます。
• ON DELETE RESTRICT: 「顧客」テーブルの顧客を削除しようとしたときに、その顧客に対する注文が「注文」テーブルに存在する場合、削除はエラーとなり阻止されます。

---

外部キー制約の作成時のエラー

• 原因とトラブルシューティング
  

  • 参照先のテーブルやカラムが存在しない
    REFERENCES で指定したテーブル名やカラム名が、実際にデータベースに存在するかどうかを確認してください。スペルミスにも注意が必要です。
  • 参照元と参照先のデータ型が一致しない
    外部キーを設定するカラムと、参照先の主キー（またはユニークキー）カラムのデータ型が完全に一致している必要があります。例えば、INT型とBIGINT型、VARCHAR型とCHAR型（長さが異なる場合も含む）などは互換性がないとみなされます。DESCRIBE テーブル名; などで各カラムのデータ型を確認しましょう。
  • 参照先のカラムにインデックスがない
    外部キーとして参照されるカラム（親テーブルの主キーまたはユニークキー）には、インデックスが設定されている必要があります。主キーには自動的にインデックスが作成されますが、ユニークキーの場合は明示的にインデックスを作成する必要があります。SHOW INDEX FROM 親テーブル; でインデックスの有無を確認し、必要であれば CREATE INDEX インデックス名 ON 親テーブル (カラム名); で作成してください。
  • テーブルのストレージエンジンが外部キーをサポートしていない
    MariaDBで一般的に使用される InnoDB ストレージエンジンは外部キーをサポートしていますが、MyISAM などの一部の古いエンジンはサポートしていません。テーブルの作成時にストレージエンジンを指定していない場合、デフォルトのエンジンが外部キーをサポートしているか確認してください。SHOW TABLE STATUS LIKE 'テーブル名'; で Engine の項目を確認できます。必要であれば ALTER TABLE テーブル名 ENGINE = InnoDB; で変更できます。
  • 構文エラー
    FOREIGN KEY 制約の定義に誤りがある場合があります。上記の「MariaDBにおける外部キーの定義方法」の構文を再度確認し、スペルミスや括弧の閉じ忘れなどがないか確認してください。
• • Cannot add foreign key constraint
  • Key column '...' doesn't exist in table '...'
  • Illegal foreign key definition
  • Referenced table '...' not found
  • Incompatible types: FK column '...' and referenced column '...'

外部キー制約によるデータ操作時のエラー

• 原因とトラブルシューティング
  

  • 子テーブルに参照先の親テーブルに存在しない値を追加・更新しようとした
    子テーブルの外部キーカラムに、親テーブルの参照カラムに存在しない値を挿入または更新しようとすると、外部キー制約に違反するためエラーが発生します。挿入・更新する前に、親テーブルに該当の値が存在することを確認してください。
  • 親テーブルに子テーブルから参照されている行が存在する状態で削除・更新しようとした
    ON DELETE RESTRICT または ON DELETE NO ACTION が設定されている場合、子テーブルにその親レコードを参照する行が存在すると、親レコードの削除は許可されません。同様に、ON UPDATE RESTRICT または ON UPDATE NO ACTION が設定されている場合、子テーブルに参照されている親レコードのキーカラムを更新することもできません。
    • 解決策
      
      • 子テーブルの関連する行を先に削除または更新する。
      • 外部キー制約の ON DELETE や ON UPDATE アクションを CASCADE や SET NULL など、より適切な動作に変更する（慎重に検討が必要です）。
  • トランザクション処理
    複数の関連するテーブルに対して操作を行う場合、トランザクションを使用することで、一時的なデータの不整合を許容し、最終的に整合性を保つことができます。エラーが発生する場合は、トランザクションの開始 (START TRANSACTION;)、操作の実行、問題なければコミット (COMMIT;)、問題あればロールバック (ROLLBACK;) を検討してください。

• エラーメッセージの例
  

  • Cannot add or update a child row: a foreign key constraint fails
  • Cannot delete or update a parent row: a foreign key constraint fails

外部キー制約の削除時のエラー

• 原因とトラブルシューティング
  

  • 指定した外部キー名が存在しない
    ALTER TABLE テーブル名 DROP FOREIGN KEY 外部キー名; で外部キーを削除する際に、指定した外部キー名が間違っているか、既に削除されている可能性があります。SHOW CREATE TABLE テーブル名; でテーブル定義を確認し、正しい外部キー名を確認してください。

• エラーメッセージの例
  

  • Cannot find constraint '...'

トラブルシューティングの一般的なヒント

• 簡単なテストケースを作成する
  問題が複雑な場合に、最小限のテーブルとデータで再現できる簡単なテストケースを作成し、原因の特定を試みます。
• MariaDB のログを確認する
  より詳細なエラー情報やバックグラウンドで発生している問題がログに記録されている場合があります。MariaDB の設定ファイルでログの出力先を確認し、内容を確認してみてください。
• SQLクエリを再確認する
  実行しようとしている INSERT、UPDATE、DELETE などの SQL クエリが、外部キー制約に違反していないか論理的に確認します。
• 関連するテーブルの構造を確認する
  DESCRIBE テーブル名; や SHOW CREATE TABLE テーブル名; を使用して、テーブルの定義、カラムのデータ型、インデックス、制約などを確認します。
• エラーメッセージを正確に読む
  エラーメッセージには、問題の原因や場所に関する重要な情報が含まれています。

---

前提

• 事前に mysql.connector などの MariaDB 接続ライブラリがインストールされている必要があります。
• ここでは、Python を使用して MariaDB に接続し、SQL クエリを実行する例を示します。他のプログラミング言語でも基本的な考え方は同じです。

-- 顧客テーブル
CREATE TABLE 顧客 (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    名前 VARCHAR(255) NOT NULL
);

-- 注文テーブル
CREATE TABLE 注文 (
    注文番号 INT PRIMARY KEY AUTO_INCREMENT,
    顧客ID INT NOT NULL,
    注文日 DATE NOT NULL,
    商品名 VARCHAR(255) NOT NULL,
    CONSTRAINT fk_顧客ID
    FOREIGN KEY (顧客ID)
    REFERENCES 顧客 (ID)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

親テーブルへのデータの挿入

まず、「顧客」テーブルにデータを挿入します。これは、後で「注文」テーブルから参照されるデータとなります。

import mysql.connector

# データベース接続情報
db_config = {
    'host': 'localhost',
    'user': 'your_user',
    'password': 'your_password',
    'database': 'your_database'
}

try:
    cnx = mysql.connector.connect(**db_config)
    cursor = cnx.cursor()

    # 顧客テーブルにデータを挿入
    add_customer = "INSERT INTO 顧客 (名前) VALUES (%s)"
    customer_data = [('山田太郎',), ('佐藤花子',)]
    cursor.executemany(add_customer, customer_data)
    cnx.commit()
    print(f"{cursor.rowcount} 件の顧客データが挿入されました。")

except mysql.connector.Error as err:
    print(f"エラー: {err}")
finally:
    if cnx.is_connected():
        cursor.close()
        cnx.close()

子テーブルへのデータの挿入 (正常なケース)

次に、「注文」テーブルに、既存の顧客IDを参照するデータを挿入します。

# ... (上記の接続処理は省略) ...

try:
    cnx = mysql.connector.connect(**db_config)
    cursor = cnx.cursor()

    # 注文テーブルにデータを挿入 (既存の顧客IDを参照)
    add_order = "INSERT INTO 注文 (顧客ID, 注文日, 商品名) VALUES (%s, %s, %s)"
    order_data = [(1, '2025-05-25', 'ノートパソコン'), (2, '2025-05-26', 'マウス')]
    cursor.executemany(add_order, order_data)
    cnx.commit()
    print(f"{cursor.rowcount} 件の注文データが挿入されました。")

except mysql.connector.Error as err:
    print(f"エラー: {err}")
finally:
    # ... (接続終了処理は省略) ...

子テーブルへのデータの挿入 (外部キー制約違反のケース)

存在しない顧客IDで「注文」テーブルにデータを挿入しようとすると、外部キー制約に違反し、エラーが発生します。

# ... (上記の接続処理は省略) ...

try:
    cnx = mysql.connector.connect(**db_config)
    cursor = cnx.cursor()

    # 注文テーブルにデータを挿入 (存在しない顧客IDを参照)
    add_order_invalid_fk = "INSERT INTO 注文 (顧客ID, 注文日, 商品名) VALUES (%s, %s, %s)"
    invalid_order_data = [(999, '2025-05-27', 'キーボード')] # 顧客ID 999 は存在しないと仮定
    cursor.execute(add_order_invalid_fk, invalid_order_data)
    cnx.commit()
    print(f"{cursor.rowcount} 件の注文データが挿入されました。") # ここは実行されないはず

except mysql.connector.Error as err:
    print(f"エラー (外部キー制約違反): {err}")
finally:
    # ... (接続終了処理は省略) ...

このコードを実行すると、「Cannot add or update a child row: a foreign key constraint fails」のようなエラーメッセージが表示されるはずです。これは、外部キー制約が正しく機能していることを示しています。

親テーブルのデータの更新 (ON UPDATE CASCADE のケース)

「顧客」テーブルの主キー (ID) を更新すると、「注文」テーブルの対応する 顧客ID も自動的に更新されます (ON UPDATE CASCADE が設定されている場合)。

# ... (上記の接続処理は省略) ...

try:
    cnx = mysql.connector.connect(**db_config)
    cursor = cnx.cursor()

    # 顧客テーブルのIDを更新
    update_customer_id = "UPDATE 顧客 SET ID = %s WHERE ID = %s"
    update_data = (10, 1) # ID 1 の顧客を ID 10 に更新
    cursor.execute(update_customer_id, update_data)
    cnx.commit()
    print(f"{cursor.rowcount} 件の顧客データが更新されました。")

    # 更新後の注文テーブルを確認 (顧客IDが 1 から 10 に変わっているはず)
    cursor.execute("SELECT * FROM 注文 WHERE 顧客ID = 10")
    orders = cursor.fetchall()
    print("更新後の注文データ:", orders)

except mysql.connector.Error as err:
    print(f"エラー: {err}")
finally:
    # ... (接続終了処理は省略) ...

親テーブルのデータの削除 (ON DELETE RESTRICT のケース)

「注文」テーブルに参照しているレコードが存在する「顧客」レコードを削除しようとすると、ON DELETE RESTRICT の設定によりエラーが発生します。

# ... (上記の接続処理は省略) ...

try:
    cnx = mysql.connector.connect(**db_config)
    cursor = cnx.cursor()

    # 顧客テーブルのデータを削除 (参照している注文が存在する場合)
    delete_customer = "DELETE FROM 顧客 WHERE ID = %s"
    delete_data = (10,) # ID 10 の顧客を削除しようとする (関連する注文が存在すると仮定)
    cursor.execute(delete_customer, delete_data)
    cnx.commit()
    print(f"{cursor.rowcount} 件の顧客データが削除されました。") # ここは実行されないはず

except mysql.connector.Error as err:
    print(f"エラー (外部キー制約違反): {err}")
finally:
    # ... (接続終了処理は省略) ...

このコードを実行すると、「Cannot delete or update a parent row: a foreign key constraint fails」のようなエラーメッセージが表示されるはずです。

---

アプリケーションレベルでの整合性チェック

外部キー制約をデータベースレベルで設定する代わりに、アプリケーションのコード内でデータの整合性をチェックする方法です。

• プログラミング例 (Python)
  
• 欠点
  
  • 複雑性の増加
    整合性チェックのロジックをアプリケーションの各所に実装する必要があるため、コードが複雑になり、保守が難しくなる可能性があります。
  • パフォーマンスへの影響
    データベースへの追加のクエリ（存在チェックなど）が発生するため、パフォーマンスが低下する可能性があります。
  • データ不整合のリスク
    アプリケーション側の実装漏れやバグにより、データの不整合が発生するリスクがあります。
• 利点
  
  • 柔軟性
    データベースの制約に縛られず、より複雑な整合性ルールを実装できます。例えば、複数の条件に基づいて参照整合性を判断したり、特定のアクションを実行したりできます。
  • 異なる種類のデータソースとの連携
    データベースだけでなく、APIやファイルなど、他のデータソースとの間で整合性を保つ必要がある場合に有効です。
• 仕組み
  データを挿入、更新、削除する前に、関連する親テーブルにデータが存在するかどうかをアプリケーション側で確認します。存在しない場合は、エラー処理を行うか、適切なデフォルト値を設定するなどの処理を行います。

def add_order_with_check(cnx, customer_id, order_date, product_name):
    cursor = cnx.cursor()

    # 顧客IDの存在チェック
    check_customer_query = "SELECT ID FROM 顧客 WHERE ID = %s"
    cursor.execute(check_customer_query, (customer_id,))
    customer = cursor.fetchone()

    if customer:
        add_order_query = "INSERT INTO 注文 (顧客ID, 注文日, 商品名) VALUES (%s, %s, %s)"
        order_data = (customer_id, order_date, product_name)
        cursor.execute(add_order_query, order_data)
        cnx.commit()
        print("注文が追加されました。")
    else:
        print(f"エラー: 顧客ID {customer_id} は存在しません。")

# ... (データベース接続処理) ...
add_order_with_check(cnx, 3, '2025-05-28', 'コーヒーメーカー')
# ... (接続終了処理) ...

トリガー (Triggers) の使用

データベースのトリガーを使用して、外部キー制約と同様の整合性チェックやアクションを自動的に実行する方法です。

• MariaDBでのトリガーの例
  
• 欠点
  
  • パフォーマンスへの影響
    トリガーの実行はオーバーヘッドになる可能性があり、特に複雑な処理を行う場合はパフォーマンスに影響を与えることがあります。
  • 保守の複雑化
    トリガーのロジックがデータベース内に隠蔽されるため、アプリケーション開発者が挙動を把握しにくくなる場合があります。
  • デバッグの困難さ
    トリガーのエラーは追跡やデバッグが難しい場合があります。
• 利点
  
  • データベースレベルでの整合性
    アプリケーションに依存せず、データベースレベルで一貫した整合性を保つことができます。
  • 複雑なビジネスロジックの実装
    外部キー制約だけでは実現できない、より複雑な条件に基づいた処理を自動化できます。
• 仕組み
  特定のテーブルで INSERT、UPDATE、DELETE などの操作が行われた際に、あらかじめ定義されたSQLコード（トリガー）が自動的に実行されます。これを利用して、参照整合性をチェックしたり、関連するテーブルのデータを操作したりできます。

-- 注文テーブルに新しいレコードが挿入される前に、顧客IDが存在するかチェックするトリガー
DELIMITER //
CREATE TRIGGER check_customer_before_insert
BEFORE INSERT ON 注文
FOR EACH ROW
BEGIN
    IF NOT EXISTS (SELECT ID FROM 顧客 WHERE ID = NEW.顧客ID) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '挿入エラー: 存在しない顧客IDです。';
    END IF;
END;
//
DELIMITER ;

-- トリガーの動作確認
INSERT INTO 注文 (顧客ID, 注文日, 商品名) VALUES (4, '2025-05-29', '電気ケトル'); -- 顧客ID 4 が存在しない場合、エラーが発生する

ストアドプロシージャ (Stored Procedures) の使用

• MariaDBでのストアドプロシージャの例
  
• 欠点
  
  • 移植性の低下
    ストアドプロシージャは特定のデータベースシステムに依存するため、異なるデータベースへの移行が難しくなる可能性があります。
  • 開発の複雑化
    ストアドプロシージャの開発には、SQLの知識に加えて、プログラミングの知識も必要となる場合があります。

DELIMITER //
CREATE PROCEDURE AddNewOrder(IN customer_id INT, IN order_date DATE, IN product_name VARCHAR(255))
BEGIN
    -- 顧客IDの存在チェック
    IF EXISTS (SELECT ID FROM 顧客 WHERE ID = customer_id) THEN
        INSERT INTO 注文 (顧客ID, 注文日, 商品名) VALUES (customer_id, order_date, product_name);
        SELECT '注文が追加されました。' AS Result;
    ELSE
        SELECT 'エラー: 存在しない顧客IDです。' AS Result;
    END IF;
END;
//
DELIMITER ;

-- ストアドプロシージャの呼び出し
CALL AddNewOrder(5, '2025-05-30', 'トースター'); -- 顧客ID 5 が存在する場合、注文が追加される
CALL AddNewOrder(999, '2025-05-30', 'ミキサー'); -- 顧客ID 999 が存在しない場合、エラーメッセージが表示される

• 保守性
  アプリケーションロジックとデータベースロジックの分離、コードの可読性などを考慮して、最適な方法を選択します。
• パフォーマンス
  アプリケーションレベルでのチェックは追加のクエリが発生するため、パフォーマンスに影響を与える可能性があります。トリガーやストアドプロシージャは、適切に設計すればパフォーマンス向上に寄与する可能性もありますが、複雑な処理はオーバーヘッドになることもあります。
• 複雑な整合性ルールやビジネスロジック
  アプリケーションレベルでのチェック、トリガー、ストアドプロシージャなどの代替方法を検討します。
• 単純な参照整合性
  データベースの標準機能である外部キー制約を使用するのが最も簡潔で効率的です。