question

Aaronsoggi-6095 avatar image
0 Votes"
Aaronsoggi-6095 asked ErlandSommarskog commented

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

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


82105-image.png

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

82202-image.png


This is the table that its conflicting with:

82104-image.png


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);              
             }
         }


sql-server-generaldotnet-csharpwindows-forms
image.png (11.1 KiB)
image.png (7.4 KiB)
image.png (111.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered TimonYang-MSFT commented

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
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes so what i would like to do is delete a row completely from the coach schedule, without the booking being deleted that contains that coachScheduleId.

so if i attempt to delete this record:

82106-image.png


I don't want any of these records to be removed from the bookings table:

82084-image.png


IWhen you say set ON DELETE CASCADE is that to be applied within the code of the table design view? (apologies I dont have alot of experience with sql)

82127-image.png


0 Votes 0 ·
image.png (5.7 KiB)
image.png (9.7 KiB)
image.png (54.9 KiB)

no worries i have solved the issue :) thankyou for the help dude really appreciate it!

0 Votes 0 ·

Hi @Aaronsoggi-6095, may I ask whether ErlandSommarskog's answer solved your problem or you solved it yourself?
If it is the former, you can accept his answer, if it is the latter, could you please share your solution and accept it?
That way, members who have the same problem in the future will be able to quickly find a solution.

1 Vote 1 ·
Aaronsoggi-6095 avatar image
0 Votes"
Aaronsoggi-6095 answered ErlandSommarskog commented

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 @ErlandSommarskog 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 @TimonYang-MSFT @ErlandSommarskog

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So this is the paradox: from this perspective, these class assignments are a lot more difficult than real business problem. In the latter case, there are users you can talk to to get the answer of what the requirements are. But when it is all made up - your guess is as good is mine.

I have never worked in this business domain, but I suspect that a real-world database for a coach service has a data model which is a little more complex.

0 Votes 0 ·