删除关联用户ID的三张表数据时触发外键约束错误求助

错误原因分析 #

你碰到的这个外键约束错误，核心问题出在删除顺序和删除范围不对：

• 错误提示明确说明：你尝试删除tbl_property中的行（父表数据），但tbl_reservation里还有记录引用了该行的propertyId（子表关联数据），InnoDB的外键约束会阻止这种破坏数据完整性的操作。
• 你当前代码里第一个DELETE语句，是删除tbl_reservation中userId = '$userId'的记录——这其实是删除该用户作为预订者的订单，但如果这个用户是房东，他名下的房源可能被其他用户预订了，这些关联的tbl_reservation记录并没有被删除，所以你删tbl_property时就触发了约束。

正确的解决方案 #

要彻底删除一个用户及其关联的所有数据，必须按照从子表到父表的顺序操作，同时覆盖所有关联的子表记录：

步骤1：删除该用户名下房源的所有预订记录

不管这些预订是谁发起的，只要房源属于要删除的用户，都得先删掉对应的预订记录：

$query = "DELETE FROM tbl_reservation 
          WHERE propertyId IN (SELECT propertyId FROM tbl_property WHERE userId = '$userId')";
$result = mysqli_query($connection, $query) or die("Error deleting property reservations: ". mysqli_error($connection));

步骤2：删除该用户作为预订者的订单（可选）

如果业务需要清理该用户自己预订他人房源的记录，再执行这一步：

$query_res = "DELETE FROM tbl_reservation WHERE userId = '$userId'";
$result_res = mysqli_query($connection, $query_res) or die("Error deleting user's reservations: ". mysqli_error($connection));

步骤3：删除该用户的房源记录

现在没有预订数据关联这些房源了，可以安全删除：

$query2 = "DELETE FROM tbl_property WHERE userId = '$userId'";
$result2 = mysqli_query($connection, $query2) or die("Error deleting properties: ". mysqli_error($connection));

步骤4：删除用户本身

最后删除用户表中的记录：

$query3 = "DELETE FROM tbl_users WHERE userId = '$userId'";
$result3 = mysqli_query($connection, $query3) or die("Error deleting user: ". mysqli_error($connection));

重要提醒：规避SQL注入风险 #

你当前的代码直接把$_GET['userId']拼入SQL语句，存在严重的SQL注入漏洞。建议改用预处理语句，示例如下：

// 预处理删除关联预订的语句
$stmt = mysqli_prepare($connection, "DELETE FROM tbl_reservation WHERE propertyId IN (SELECT propertyId FROM tbl_property WHERE userId = ?)");
mysqli_stmt_bind_param($stmt, "i", $userId);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);

// 后续的删除操作也用类似的预处理方式

完整修改后的代码 #

<?php
session_start();
$userId = $_GET['userId'];
require_once('databaseConn.php');

// 1. 删除该用户房源的所有预订记录
$stmt1 = mysqli_prepare($connection, "DELETE FROM tbl_reservation WHERE propertyId IN (SELECT propertyId FROM tbl_property WHERE userId = ?)");
mysqli_stmt_bind_param($stmt1, "i", $userId);
mysqli_stmt_execute($stmt1) or die("Error deleting property reservations: ". mysqli_error($connection));
mysqli_stmt_close($stmt1);

// 2. 可选：删除该用户自己的预订记录
$stmt2 = mysqli_prepare($connection, "DELETE FROM tbl_reservation WHERE userId = ?");
mysqli_stmt_bind_param($stmt2, "i", $userId);
mysqli_stmt_execute($stmt2) or die("Error deleting user's reservations: ". mysqli_error($connection));
mysqli_stmt_close($stmt2);

// 3. 删除用户的房源
$stmt3 = mysqli_prepare($connection, "DELETE FROM tbl_property WHERE userId = ?");
mysqli_stmt_bind_param($stmt3, "i", $userId);
mysqli_stmt_execute($stmt3) or die("Error deleting properties: ". mysqli_error($connection));
mysqli_stmt_close($stmt3);

// 4. 删除用户
$stmt4 = mysqli_prepare($connection, "DELETE FROM tbl_users WHERE userId = ?");
mysqli_stmt_bind_param($stmt4, "i", $userId);
mysqli_stmt_execute($stmt4) or die("Error deleting user: ". mysqli_error($connection));
mysqli_stmt_close($stmt4);

header('Location: index.php');
?>

内容的提问来源于stack exchange，提问作者johnny