Delete the record from table tuto with TutorID=104. Examine data in table MATCH_

Question

Delete the record from table tuto with TutorID=104. Examine data in table MATCH_HISTORY.   You will find that all records for TutorID =104 have also been dropped. We have therefore lost the history about tutoring students with Student ID 6 and 7. Why is that the case? Is there a way for us to prevent deleting records in table tuto or table STUDENT while there are matching records in MATCH_HISTORY?

FIGURE 6-12 Adult literacy program (for Problems and Exercises 6-45 through 6-53) TUTOR TutorID, CertDate, Status) STUDENT (StudentlD, Read) TutorID Status 100 1/05/2015 101 1/05/2015 102 1/05/2015 103 5/22/2015 104 5/22/2015 105 5/22/2015 106 5/22/2015 3000 3001 3002 3003 3004 2.3 5.6 1.3 3.3 2.7 4.8 7.8 1.5 Temp Stop Temp Stop 3006 3007 MATCH HISTORY (MatchID, TutoriD, StudentlD, StartDate, EndDate) MatchID TutorlD 100 1/10/2015 101 102 106 1/15/2015 3002 2/10/2015 3003 5/28/2015 5/15/2015 3/01/2015 103 104 104 3004 6/01/2015 3005 6/01/2015 3006 6/01/2015 6/15/2015 6/28/2015

Explanation / Answer

Solution :

When a foreign key is defined on the table there is one attribute that is to be set on the foreign key, that attribute is cascade delete.
Cascade delete means when we delete the row in parent table the corresponding row in child table will also be deleted.
For example here XYZTutor table is a parent table and XYZTutorID in MATCH_HISTORY is a foreign key which has reference on XYZTutor table.
So when foriegn key was defined in MATCH_HISTORY the cascade delete is set.
Hence when you delete one row from XYZTutor it also deletes corresponding row from MATCH_HISTORY.

If you dont want the record from MATCH_HISTORY to be deleted then dont mention "ON DELETE CASCADE" while defining the foreign key.

one more thing you can do is : put "Set null on delete" on foreign key.
SO when you define foreign key put "set null on delete". This will not delete row from MATCH_HISTORY but it will set foreign key value as null.
In your example XYZTutorID will be null for the students whose records are deleted.

If you dont want to set it to null then apply "Drop foriegn key" on the table for XYZTutorID.

Basically when a row from XYZTutor is deleted then there is no reference for that XYZTutorID. So it is advised to delete the row or set XYZTutorID to null in such cases.

If you want to access XYZTutor information but want to deactivate the XYZTutor or something then you can add one more attribute on XYZTutor table that will store if the XYZTutor Is active or not.
So instead of deleting the row just set that row to "deactivated". This way MATCH_HISTORY can access previous data and XYZTutor is deactivated too.

if you have any doubts then you can ask here. if you find the solution helpful then please upvote the answer. Thank you.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.