MySQL ON DELETE CASCADE or SET NULL

Learn how to use referential actions for a foreign key to delete data from child tables when you delete data from a parent table and when is appropriate to set the value to null or leave default.



Introduction

I'm here to tell you about referential actions when defining foreign keys in your database migration files in Laravel.

The reason why I want to talk about this and explain what does this do is because I have been kicked in the ass by it during the refactoring of my website Laravelista.

This is what happened to me

I had some understanding of how cascading works when deleting parent tables and how it reflects on the child tables. The most simple example would be this. When you delete an author and if you have cascade on delete set up for his posts, you would actually delete that author from the authors table and automatically all posts which were written by that author. This saves you a lot of time. In your code, you no longer need to first delete the posts from that author and then the author. You just delete the author and automatically the posts are also deleted. If you think about it this makes perfect sense to use everywhere at first. At least that is what I have done for my tutorials and courses. When I deleted a courses it deleted all the tutorials that belonged to that courses. What this comes down to is your business logic and how you want things to happen. For me, this was unacceptable because tutorials are the most important thing for me, not the courses.

So I asked the Internet what other options do I have for this. I found out that you can also use SET NULL instead of CASCADE and what does that do is it sets the value of that foreign key to null, meaning that in my case the tutorial that belonged to the courses that is being deleted now, does not belong to any courses anymore.

Another approach that you could take is to leave out the ON DELETE clause and before deleting a courses manually set the value of the foreign key on tutorials to null. This requires a little bit more code from your part but is still legit.

In the next chapters, I will show you how to set up and use the above mentioned referential actions in your database migration files in Laravel.

CASCADE

To demonstrate when you would use CASCADE referential action I will use the following example. Let's say that we have authors and posts tables. An author can have many posts, while a single post can only belong to a single author.

Now the business rule: "If the author of the post is deleted, delete all the posts that he has written".

To solve this we would create a migration like this:

Schema::table('posts', function ($table) {
    // ...
    $table->integer('author_id')->unsigned();
    $table->foreign('author_id')
      ->references('id')->on('authors')
      ->onDelete('cascade');
});

SET NULL

To demonstrate SET NULL referential action I will use a different example because it does not make sense to set the author of the post to null. If your business rule requires it so, then you could use it there, but I will use a better example.

Let's say that we now have two tables tutorials and courses. One tutorial can belong to only one courses and one courses can have many tutorials.

Now the business rule: "If the courses is deleted, orphan the tutorials that belonged to that courses. What I wanted to say here is to set the course_id value to null, meaning that the tutorial that has previously belonged to a courses, now no longer belongs to any courses and is independent.

To solve this we would create a migration like this:

Schema::table('tutorials', function ($table) {
    // ...
    $table->integer('course_id')->unsigned();
    $table->foreign('course_id')
      ->references('id')->on('courses')
      ->onDelete('set null');
});

I hope that I have managed to bring closer this topic to you and that you will take it into consideration the next time you write a database migration file.

Feel free to ask me more about this topic or are still unclear on something.

Credits

Comments