解决停车预约系统中INSERT与FOREIGN KEY约束冲突问题

这个错误的核心是你要插入的UserID在Users表中不存在，违反了ParkingReservations表与Users表之间的外键约束。以下是具体排查和解决步骤：

1. 先确认传入的UserID有效性 #

• 检查调用ReserveParkingSpot方法时，传入的Reservation对象的UserID值：
  • 是否传入了0、null这类无效值？
  • 该UserID对应的用户是否已被正确插入到Users表中？
• 可临时在代码中添加输出，确认传入值：

  Console.WriteLine($"传入的UserID：{reservation.UserID}");
  

2. 补充前置校验逻辑 #

在执行预约操作前，先校验UserID和VehicleID是否存在对应记录，提前拦截错误：

// 检查UserID是否存在
string checkUserQuery = "SELECT COUNT(*) FROM Users WHERE UserID = @UserID";
using (SqlCommand checkUserCmd = new SqlCommand(checkUserQuery, connection))
{
    checkUserCmd.Parameters.AddWithValue("@UserID", reservation.UserID);
    int userCount = (int)checkUserCmd.ExecuteScalar();
    if (userCount == 0)
    {
        Console.WriteLine($"UserID {reservation.UserID} 不存在，无法创建预约");
        return false;
    }
}

// 检查VehicleID是否存在
string checkVehicleQuery = "SELECT COUNT(*) FROM Vehicles WHERE VehicleID = @VehicleID";
using (SqlCommand checkVehicleCmd = new SqlCommand(checkVehicleQuery, connection))
{
    checkVehicleCmd.Parameters.AddWithValue("@VehicleID", reservation.VehicleID);
    int vehicleCount = (int)checkVehicleCmd.ExecuteScalar();
    if (vehicleCount == 0)
    {
        Console.WriteLine($"VehicleID {reservation.VehicleID} 不存在，无法创建预约");
        return false;
    }
}

3. 用事务保证操作原子性 #

当前代码中UPDATE ParkingSpots和INSERT ParkingReservations是独立执行的，若插入失败，车位状态会被错误标记为已占用。用事务包裹两个操作，保证要么全部成功，要么全部回滚：

using (SqlTransaction transaction = connection.BeginTransaction())
{
    try
    {
        // 更新车位状态
        string updateSpotQuery = "UPDATE ParkingSpots SET IsFree = 0 WHERE ParkingSpotID = @ParkingSpotID";
        using (SqlCommand updateCmd = new SqlCommand(updateSpotQuery, connection, transaction))
        {
            updateCmd.Parameters.AddWithValue("@ParkingSpotID", reservation.ParkingSpotID);
            updateCmd.ExecuteNonQuery();
        }

        // 插入预约记录
        string insertReservationQuery = "INSERT INTO ParkingReservations (UserID, VehicleID, ParkingSpotID, StartTime, EndTime) " +
                                        "VALUES (@UserID, @VehicleID, @ParkingSpotID, @StartTime, @EndTime); " +
                                        "SELECT SCOPE_IDENTITY();";
        using (SqlCommand insertCmd = new SqlCommand(insertReservationQuery, connection, transaction))
        {
            insertCmd.Parameters.AddWithValue("@UserID", reservation.UserID);
            insertCmd.Parameters.AddWithValue("@VehicleID", reservation.VehicleID);
            insertCmd.Parameters.AddWithValue("@ParkingSpotID", reservation.ParkingSpotID);
            insertCmd.Parameters.AddWithValue("@StartTime", reservation.StartTime);
            insertCmd.Parameters.AddWithValue("@EndTime", reservation.EndTime);

            int reservationID = Convert.ToInt32(insertCmd.ExecuteScalar());
            Console.WriteLine($"预约成功，预约ID：{reservationID}");
        }

        transaction.Commit();
        return true;
    }
    catch (Exception ex)
    {
        transaction.Rollback();
        Console.WriteLine($"预约失败：{ex.Message}");
        return false;
    }
}

4. 数据库层面额外检查 #

• 执行SELECT * FROM Users确认表中存在对应UserID的记录；
• 核对外键约束定义，确保ParkingReservations.UserID确实关联Users.UserID（从你的SQL脚本看定义正确，但可再次确认）。

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