Fix INSERT conflict with FOREIGN KEY constraint issue

Question

I'm making a parking reservation system for a project and I'm encountering an issue when trying to insert my data into the database. I'm using C# and ADO.NET

The exact error is:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__ParkingRe__UserI__2057CCD0". The conflict occurred in database "Reservation", table "dbo.Users", column 'UserID'

public bool ReserveParkingSpot(Reservation reservation)
{
    try
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // Check if the parking spot is available during the specified time
            string availabilityCheckQuery = "SELECT COUNT(*) FROM ParkingReservations " +
                                            "WHERE ParkingSpotID = @ParkingSpotID " +
                                            "AND ((@StartTime >= StartTime AND @StartTime < EndTime) OR " +
                                            "(@EndTime > StartTime AND @EndTime <= EndTime))";
            using (SqlCommand availabilityCheckCommand = new SqlCommand(availabilityCheckQuery, connection))
            {
                availabilityCheckCommand.Parameters.AddWithValue("@ParkingSpotID", reservation.ParkingSpotID);
                availabilityCheckCommand.Parameters.AddWithValue("@StartTime", reservation.StartTime);
                availabilityCheckCommand.Parameters.AddWithValue("@EndTime", reservation.EndTime);

                int overlappingReservations = (int)availabilityCheckCommand.ExecuteScalar();

                if (overlappingReservations > 0)
                {
                    Console.WriteLine("Parking spot is not available during the specified time.");
                    return false;
                }
            }

            // If the parking spot is available, update the IsFree status and proceed with the reservation
            string reserveParkingQuery = "UPDATE ParkingSpots SET IsFree = 0 WHERE ParkingSpotID = @ParkingSpotID; " +
                                         "INSERT INTO ParkingReservations (UserID, VehicleID, ParkingSpotID, StartTime, EndTime) " +
                                         "VALUES (@UserID, @VehicleID, @ParkingSpotID, @StartTime, @EndTime); " +
                                         "SELECT SCOPE_IDENTITY();"; // Retrieve the auto-incremented ReservationID
            using (SqlCommand reserveParkingCommand = new SqlCommand(reserveParkingQuery, connection))
            {
                reserveParkingCommand.Parameters.AddWithValue("@UserID", reservation.UserID);
                reserveParkingCommand.Parameters.AddWithValue("@VehicleID", reservation.VehicleID);
                reserveParkingCommand.Parameters.AddWithValue("@ParkingSpotID", reservation.ParkingSpotID);
                reserveParkingCommand.Parameters.AddWithValue("@StartTime", reservation.StartTime);
                reserveParkingCommand.Parameters.AddWithValue("@EndTime", reservation.EndTime);

                // Retrieve the auto-incremented ReservationID
                int reservationID = Convert.ToInt32(reserveParkingCommand.ExecuteScalar());

                Console.WriteLine($"Parking spot with ID {reservationID} reserved successfully!");
                return true;
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Error reserving parking spot: {ex.Message}");
        return false;
    }
}

CREATE TABLE Users 
(
    UserID INT PRIMARY KEY IDENTITY(1,1),
    [Name] VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    [Password] VARCHAR(255) NOT NULL
);

CREATE TABLE Vehicles 
(
    VehicleID INT PRIMARY KEY IDENTITY(1,1),
    Brand VARCHAR(50) NOT NULL,
    Model VARCHAR(50) NOT NULL,
    [Year] INT NOT NULL,
    RegistrationPlate VARCHAR(20) UNIQUE NOT NULL
);

CREATE TABLE ParkingReservations 
(
    ReservationID INT PRIMARY KEY IDENTITY(1,1),
    UserID INT FOREIGN KEY REFERENCES Users(UserID),
    VehicleID INT FOREIGN KEY REFERENCES Vehicles(VehicleID),
    ParkingSpotID INT,
    StartTime DATETIME,
    EndTime DATETIME,
    FOREIGN KEY (ParkingSpotID) REFERENCES ParkingSpots(ParkingSpotID)
);

CREATE TABLE ParkingSpots 
(
    ParkingSpotID INT PRIMARY KEY IDENTITY(1,1),
    IsFree BIT
);

INSERT INTO ParkingSpots (IsFree) 
VALUES (1), (1), (1), (1), (1),
       (1), (1), (1), (1), (1),
       (1), (1), (1), (1), (1),
       (1), (1), (1), (1), (1),
       (1), (1), (1), (1), (1);

I tried rewriting the whole query, but to no avail. It needs to save the data to the ParkingReservations table

Answer 1

I saw that you tried to repost this question so I gave it a second look. It appears that your actual problem is that you are creating the ParkingSpots table after trying to reference it in the foreign key specified in the definition for ParkingReservations.

If you move the definition for ParkingSpots before the definition of ParkingReservations it should solve your issue.

CREATE TABLE Users (
    UserID INT PRIMARY KEY IDENTITY(1,1),
    [Name] VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    [Password] VARCHAR(255) NOT NULL
);

CREATE TABLE Vehicles (
    VehicleID INT PRIMARY KEY IDENTITY(1,1),
    Brand VARCHAR(50) NOT NULL,
    Model VARCHAR(50) NOT NULL,
    [Year] INT NOT NULL,
    RegistrationPlate VARCHAR(20) UNIQUE NOT NULL
);


CREATE TABLE ParkingSpots (
    ParkingSpotID INT PRIMARY KEY IDENTITY(1,1),
    IsFree BIT
);

CREATE TABLE ParkingReservations (
    ReservationID INT PRIMARY KEY IDENTITY(1,1),
    UUID INT FOREIGN KEY REFERENCES Users(UserID),
    CarID INT FOREIGN KEY REFERENCES Vehicles(VehicleID),
    SpotID INT,
    StartTime DATETIME,
    EndTime DATETIME,
    FOREIGN KEY (SpotID) REFERENCES ParkingSpots(ParkingSpotID)
);

INSERT INTO ParkingSpots (IsFree) VALUES (1), (1), (1), (1), (1),
                                         (1), (1), (1), (1), (1),
                                         (1), (1), (1), (1), (1),
                                         (1), (1), (1), (1), (1),
                                         (1), (1), (1), (1), (1)

As mentioned in the comments above by Thom A it would be good to get in the habit of specifying names for your foreign keys. This can save a lot of issues in the long run.

If you wish to name your constraints simply replace

FOREIGN KEY (ParkingSpotID) REFERENCES ParkingSpots(ParkingSpotID)

with

CONSTRAINT FK_ParkingReservations_ParkingSpots FOREIGN KEY (SpotID) REFERENCES ParkingSpots(ParkingSpotID)   

Where FK_ParkingReservations_ParkingSpots is the name of the constraint