Cascade update
Another feature of the relationship is what can happen when the "parent" table changes. If a doctor's ID changes in the doctor table then that could mean that all the appointments with the old ID are no longer connected to a doctor (normally called orphaned records). Most relational databases will prevent that from happening by offering to automatically cascade the change to the child table. So now if a doctor ID changes that matching ID will be changed in all appointments as well. This is often referred to as CASCADE UPDATE. MySQL also allows you to prevent that change being made at all (CASCADE RESTRICT).
Delete restrict
Imagine a doctor. The doctor has many appointments over years. Then they retire. Their record is deleted from the Doctor table. All of the appointments recorded for that doctor now have no related doctor record in the Doctor table. This might be acceptable to the users but it is not ideal. There are three main ways to handle this:
- leave the orphaned appointment records without a related doctor
- delete all related appointments when the doctor's record is deleted (CASCADE DELETE them)
- refuse to allow anyone to delete a doctor who has related appointments (RESTRICT DELETE)
The last choice is best as the doctor could always be marked as inactive and hidden from most views of the data. Then the data would still be available if it was need later on.
Obviously there is no problem with deleting a child record (the appointment) and leaving the doctor undeleted. You certainly would not want to delete the doctor's record every time an appointment was cancelled! Cascade delete always works from the "one" side of the relationship to the "many" side.
There are occasions when cascade delete would be useful (when data would no longer be needed).
Conclusions
Try to get your head around these concepts. Most are just common sense but the terms may confuse the issue. The most common decision is to cascade update any changes but to RESTRICT parent records from deletion if there are any child records. This is also the safest choice.
