Here is the video going over the examples in this post (It may be easier to visualize by watching the examples play out) :

Before we dive in, I have the database schema included here below for easy reference just in case it helps (Note that the highlighted fields in bright yellow are from the adjustments that I made to the original set of data before I populated the database. It won’t affect anything here.):

Let’s review some foreign key constraints, specifically, ON DELETE SET NULL and ON DELETE CASCADE. We briefly touched on this topic when we were initially creating the tables for our Yoga Database. It may be helpful to review that particular post for a quick refresh of all the code used to set up each table. (Here is the link to that post for reference: https://cattylearnscode.com/2023/05/02/creating-all-tables-for-our-yoga-database/ ). For today, let’s get a better understanding with examples of how these constraints actually function and play out when we delete data.

First, let’s go over what each of these foreign key constraints will do:

• ON DELETE SET NULL – If you delete any of the records from the parent table, the specific foreign key fields associated in the child table will be set to NULL.

• ON DELETE CASCADE – If you delete any of the records from the parent table, the rows that are correlated through the foreign key fields in the child table will be deleted.

So now, we will go over an example deleting data from our yoga_teacher table:

DELETE FROM yoga_teacher
WHERE teacher_id = 1116;

This code will delete the row in the yoga_teacher table for the teacher that has the id of 1116. However, because the manager_id column is a foreign key that references the teacher_id column in the same table and has the constraint of ON DELETE SET NULL, any of the fields in the manager_id column that has 1116 will now be set to NULL.

This will also affect the studio table, since the manager_id column is a foreign key in that table referencing the teacher_id in the yoga_teacher table. So, the field that reads 1116 in the manager_id column, which is for the Culver City location, will be set to NULL.

Let’s now take a look at the next example, which deletes data from the studio table.

DELETE FROM studio
WHERE studio_id = 2;

By deleting the row that contains studio_id of 2, this will affect the yoga_teacher table since its studio_id column serves as a foreign key with the constraint ON DELETE SET NULL. So if any of the studio_id fields contain the id of 2, they will be set to NULL.

The class_offered table will be affected as well because it has a studio_id column referencing the studio_id column in the studio table. So all the fields in the studio_id column in the class_offered table that reads 2 will become NULL.

A few different things will occur in the membership_plan table. The studio_id column is a foreign key that references studio_id in the studio table. However, in this case, the studio_id and plan_type columns together serve as a composite key for the membership_plan table. Therefore, the constraint for the studio_id column has been set for ON DELETE CASCADE. This is important because we wouldn’t want to set a NULL constraint in this situation since studio_id and plan_type are both primary keys and only together do they serve as the unique identifier for this table. Instead, we would want the rows that are associated with any deleted rows within the parent table to be deleted here as well.

The taught_by table would carry out a similar action, since the teacher_id and class_id together serve as the composite key. The teacher_id is a foreign key referencing the teacher_id column of the yoga_teacher table and we set the constraint as ON DELETE CASCADE. Because our first example deleted the row with the teacher_id of 1116 from the yoga_teacher (parent table), any rows associated with the teacher_id of 1116 in the taught_by (child table) will be deleted as well.

Alright, that’s all for now. We can see how there is quite the domino effect that occurs with our constraints just from the two examples of deleting data since all our tables are connected to other tables through primary keys and foreign keys.

I hope this is helpful. Feel free to watch the related video. It may be easier to visualize and understand the examples.

Thanks for learning with me, friends :).