How would i delete a record from a table which is the child of another table.

Question

I'm getting the following error when attempting to delete data from a table:

Here is the coachSchedule table which is where I'm attempting to delete data from:

This is the table that its conflicting with:

Is there way that i can delete data from the coach schedule table without getting the error above?

Below is my current query:

  private void button2_Click(object sender, EventArgs e)  
        {  
            try  
            {  
                string query = "DELETE cs from coachSchedule cs where coachScheduleId= @coachScheduleId";  
                cmd = new SqlCommand(query, _isqlDataFunctions.GetConnection());  
                cmd.Parameters.AddWithValue("@coachScheduleId", scheduleId);      
  
                _isqlDataFunctions.ManagingData(cmd, "Coach schedule has been deleted.");  
                clearFields();  
                DisplayAllSchedules();  
                button1.Enabled = false;  
                button2.Enabled = false;  
                  
            }  
            catch(Exception ex)   
            {                
                MessageBox.Show(ex.Message);                
            }  
        }  

Answer 1

It seems that you have set up a foreign-key constraint like this:

ALTER TABLE Bookings ADD CONSTRAINT FK_Bookings_Coachschedule
FOREIGN KEY (scheduleID) REFERENCES coachSchedule(coachScheduleId)
ON UPDATE NO ACTION ON DELETE NO ACTION

This constraint says that for a row in Bookings, the schedule must exist in the coachSchedule table. So when you try to delete a schedule for which there is a booking, SQL Server sounds the alarm.

It is unclear what you want to achieve. In the subject line you talk about "child table". I am not sure that I would call any table here a child, since a schedule is one thing and a booking is another. But if one is a child it is the booking since this the table with the FK constraint, and thus the schedule is the parent.

Now, it could be that if you delete the schedule that you want the bookings to be delete (but that does not really seem good for business), and in such case you can specify the constraint as

ON DELETE CASCADE

Yet an alternative is to just set NULL in the scheduleId column but keep the row. For this you can use this option:

ON DELETE SET NULL

Answer 2

Sorry Timon, yes it did solve my answer, i had a bit of a problem when deleting data using ON DELETE SET NULL. it would keep the record in the bookings table which was great however removing the foreign key from the bookings data prevented some my INNER JOIN queries from working. (I was joining information from the bookings table and coach schedule). My deadline is tomorrow so i don't want to change too many things around at this point. I decided to go with DELETE CASCADE which solved my problem, but as @Erland Sommarskog mentioned from a business perspective i wouldn't say its the best option.

I think i may have a better solution which I'm going to implement today. I might remove the delete function completely, and instead only allow the user to update and add schedules to the database. I will then add a "status" attribute to the schedule table.

This would result in the database having a long list of schedules that cannot be deleted, however in a real life situation there are always going to be schedules that are available/unavailable and updated, so I'm thinking that this solution may not be too bad. by having the status attribute it simply informs the customer whether or not they can make a booking. If not ill display a message along the lines of "Unfortunately this journey is temporarily unavailable"

It would be great to get a second opinion on this change, do you think it would make sense to do this? or should i keep the remove function @Timon Yang-MSFT @Erland Sommarskog