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 on 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 lessons and series.. When I deleted a series it deleted all the lessons that belonged to that series. What this comes down to is your business logic and how you want things to happen. For me this was unacceptable because lessons are the most important thing for me, not the series.

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 lesson that belonged to the series that is being deleted now, does not belong to any series anymore.

Another approach that you could take is to leave out the ON DELETE clause and before deleting a series, manually set the value of the foreign key on lessons 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. 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 you 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 lessons and series. One lesson can belong to only one series and one series can have many lessons.

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

To solve this we would create a migration like this:

Schema::table('lessons', function ($table) {
    // ...
    $table->integer('serie_id')->unsigned();
    $table->foreign('serie_id')
      ->references('id')->on('series')
      ->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