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

外部キーとは？

外部キーは、2つのテーブル間の関係を定義するデータベースオブジェクトです。親テーブルのプライマリキーまたはユニークキーを参照することで、子テーブルのデータ整合性を確保します。

1. テーブルの作成:

   • まず、親テーブルと子テーブルを作成します。
   • 親テーブルには、外部キーの参照先のプライマリキーまたはユニークキーを作成します。

2. 外部キーの追加:

   • 子テーブルの構造タブで、「インデックス」をクリックします。
   • 「インデックスを追加」ボタンをクリックします。
   • インデックス名を入力し、「キー列」で参照する親テーブルの列を選択します。
   • 「アクション」で「外部キーの定義」を選択します。
   • 「参照テーブル」に親テーブルの名前を入力し、「参照列」に参照するプライマリキーまたはユニークキーを選択します。
   • 「更新アクション」と「削除アクション」を設定します。
     • CASCADE: 親テーブルのレコードが更新または削除された場合、子テーブルの関連するレコードも更新または削除されます。
     • RESTRICT: 親テーブルのレコードが更新または削除された場合、子テーブルの関連するレコードが存在すれば操作は失敗します。
     • SET NULL: 親テーブルのレコードが更新または削除された場合、子テーブルの関連するレコードの外部キー列がNULLになります。
   • 「保存」ボタンをクリックして、外部キーの設定を保存します。

例

親テーブル: users (id: int, name: varchar) 子テーブル: orders (id: int, user_id: int, amount: decimal)

ordersテーブルのuser_id列に外部キーを設定します。

• user_id列のインデックスを追加し、「外部キーの定義」を選択します。
• 「参照テーブル」にusers、「参照列」にidを設定します。
• 「更新アクション」と「削除アクション」を適切に選択します。

これにより、ordersテーブルのuser_id列は常にusersテーブルのid列と一致するようになり、データ整合性が確保されます。

外部キー設定の例: PHPMyAdminとMySQL

PHPMyAdminでの外部キー設定

-- 親テーブル: users
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

-- 子テーブル: orders
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

• FOREIGN KEY (user_id) REFERENCES users(id): ordersテーブルのuser_id列がusersテーブルのid列を参照する外部キーであることを指定します。
• ON DELETE CASCADE: usersテーブルのレコードが削除された場合、それに関連するordersテーブルのレコードも削除されます。

MySQLでの外部キー設定

-- 親テーブル: users
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

-- 子テーブル: orders
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    CONSTRAINT fk_orders_users FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

• 他の部分はPHPMyAdminでの設定と同じです。

外部キー設定の解説

• 外部キーは、2つのテーブル間の関係を定義するデータベースオブジェクトです。
• 親テーブルのプライマリキーまたはユニークキーを参照することで、子テーブルのデータ整合性を確保します。
• 外部キーの設定は、テーブルの作成時にまたは既存のテーブルに対して行うことができます。
• ON DELETEとON UPDATEオプションを使用して、親テーブルのレコードが更新または削除された場合の子テーブルのレコードに対する動作を指定できます。

外部キー設定の代替方法

外部キーは、データベースのデータ整合性を確保するための重要な機能です。しかし、特定の状況では、外部キーを使用しない代替方法が考慮されることがあります。

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

• 利点: 外部キーの制約をデータベースレベルではなく、アプリケーションレベルで実装できるため、柔軟性が高くなります。
• 欠点: アプリケーションのロジックが複雑になり、データ整合性を維持するための追加の処理が必要になります。

// 例: PHPでのチェック
$userId = $_POST['user_id'];

// データベースにクエリを実行して、指定されたユーザーが存在するかを確認
$query = "SELECT * FROM users WHERE id = ?";
$stmt = $pdo->prepare($query);
$stmt->execute([$userId]);

if (!$stmt->fetch()) {
    // ユーザーが存在しない場合の処理
    echo "Invalid user ID";
} else {
    // ユーザーが存在する場合、注文を挿入
    $query = "INSERT INTO orders (user_id, amount) VALUES (?, ?)";
    $stmt = $pdo->prepare($query);
    $stmt->execute([$userId, $_POST['amount']]);
}

トリガーの使用

• 利点: データベースレベルで外部キーの制約を実装する代わりに、トリガーを使用してデータの整合性をチェックできます。
• 欠点: トリガーの複雑さが増す可能性があり、パフォーマンスに影響を与えることがあります。

-- 例: MySQLのトリガー
CREATE TRIGGER before_insert_order
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE user_exists INT;

    SELECT COUNT(*) INTO user_exists FROM users WHERE id = NEW.user_id;

    IF user_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid user ID';
    END IF;
END;

ストアドプロシージャの使用

• 利点: 外部キーの制約をストアドプロシージャで実装することで、データベースレベルでの処理をカプセル化できます。

-- 例: MySQLのストアドプロシージャ
CREATE PROCEDURE insert_order(IN user_id INT, IN amount DECIMAL(10,2))
BEGIN
    DECLARE user_exists INT;

    SELECT COUNT(*) INTO user_exists FROM users WHERE id = user_id;

    IF user_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid user ID';
    ELSE
        INSERT INTO orders (user_id, amount) VALUES (user_id, amount);
    END IF;
END;