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

1
2
3

-- usersテーブルにid=999は存在しない
INSERT INTO orders (user_id, amount) VALUES (999, 1000);
-- Error: Cannot add or update a child row

1
2
3

-- このユーザーを参照しているordersがある
DELETE FROM users WHERE id = 1;
-- Error: Cannot delete or update a parent row

1
2
3

-- usersのidがINTなのに、ordersのuser_idがBIGINT
ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);
-- Error: Cannot add foreign key constraint

1
2
3
4
5

-- 先にユーザーを作成
INSERT INTO users (id, name) VALUES (1, 'John');

-- その後で注文を作成
INSERT INTO orders (user_id, amount) VALUES (1, 1000);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

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

-- 既存テーブルに追加
ALTER TABLE orders
DROP FOREIGN KEY orders_user_id_foreign;

ALTER TABLE orders
ADD CONSTRAINT orders_user_id_foreign
FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

1
2
3
4
5
6

-- 親が削除されたらNULLにする
ALTER TABLE orders
ADD CONSTRAINT orders_user_id_foreign
FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 外部キーチェックを無効化（データ移行時など）
SET FOREIGN_KEY_CHECKS = 0;

-- データ操作
INSERT INTO orders (user_id, amount) VALUES (999, 1000);

-- 外部キーチェックを再有効化
SET FOREIGN_KEY_CHECKS = 1;

-- 警告: 整合性が崩れる可能性があるため、本番では慎重に使用

1
2
3
4
5
6
7
8
9

-- 子テーブルから先に削除
DELETE FROM orders WHERE user_id = 1;
DELETE FROM users WHERE id = 1;

-- トランザクションで一括
START TRANSACTION;
DELETE FROM orders WHERE user_id = 1;
DELETE FROM users WHERE id = 1;
COMMIT;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- テーブルの外部キーを確認
SHOW CREATE TABLE orders;

-- 制約の詳細を確認
SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'orders' AND CONSTRAINT_TYPE = 'FOREIGN KEY';

-- 参照関係を確認
SELECT * FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'orders' AND REFERENCED_TABLE_NAME IS NOT NULL;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

// マイグレーション
Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')
        ->constrained()
        ->onDelete('cascade');
    $table->decimal('amount', 10, 2);
});

// 外部キーを削除
Schema::table('orders', function (Blueprint $table) {
    $table->dropForeign(['user_id']);
});

1
2
3
4
5
6
7
8

class Order(models.Model):
    user = models.ForeignKey(
        User,
        on_delete=models.CASCADE,  # 親削除時に子も削除
        # on_delete=models.SET_NULL, null=True,  # NULLに設定
        # on_delete=models.PROTECT,  # 削除を禁止
    )
    amount = models.DecimalField(max_digits=10, decimal_places=2)

1
2
3
4
5

Order.belongsTo(User, {
    foreignKey: 'user_id',
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE'
});

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 孤立したレコードを検出
SELECT o.* FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;

-- 不整合データを修正
DELETE FROM orders
WHERE user_id NOT IN (SELECT id FROM users);

-- または NULL に設定
UPDATE orders
SET user_id = NULL
WHERE user_id NOT IN (SELECT id FROM users);