PHP Laravel – emptying database during seeding with foreign key constraints

It was little over a decade ago when I first began my developing career in some of the most basic of web-based projects. Back in those days, I was primarily based in web-development for a variety of projects including DW4N and a range of forums. In this challenge, PHP was my primary language of use, back in the day when the server could provide us with most of our functionality that we wished, and long before websites became focused on the sleek and responsive layouts which have become commonplace now. However, my PHP projects were limited to small, one-off sites or scripts for completing a specific purpose with re-usability of code not being given much consideration.

Recently, following about 4 years away from PHP web-development, I have returned to the PHP fold for some light personal projects to find that practically the entire web-development industry has moved on. Frameworks and libraries that were mere pipe-dreams 4 years ago have since become reality and are being used in force within the web-development industry, powering many a website these days with ease.

As part of my little side-project I’ve been working with the Laravel framework to handle the majority of my work. Thankfully, while the frameworks and libraries have moved on, the language of PHP is relatively the same as it was when I last used it, so picking it back again has been easy so far, with only the odd minor nuance. One of these nuances has been trying to seed my database tables without having to reset the migration and re-populate the tables.

Emptying a table

Emptying a table is relatively straight forward, with a nice command called ‘truncate’ available on your model which will empty it of all current rows. The command is called like so:

YourModelName::truncate();

SQLState[42000] – Syntax error or access violation – Cannot truncate a table referenced in a foreign key constraint

However, if you try to empty a table that has foreign key constraints in child tables, then you might encounter this error when you try to seed your database and empty the current data.

To get around this, you can turn the foreign key checks off during the seeding by calling the ‘statement’ command and setting the foreign key check flag to 0, like so:

DB::statement('SET FOREIGN_KEY_CHECKS=0;');

This is supposed to apply to only a single connection, however, for safety you can reset the checks to occur again after you’ve finished like so:

DB::statement('SET FOREIGN_KEY_CHECKS=1;');

Between these calls you can truncate the table as necessary and re-seed it with the fresh data.

Some words of warning

During a production app you wouldn’t generally want to disable the foreign key checks – they exist for a reason to protect the database and the data in it from being inadvertently disposed so these shouldn’t be used in the production code. However, you typically wouldn’t be seeding a database that is in production mode either, so using these commands for development purposes is likely to be ok. But that being said, do be careful of keeping them out of the production code where necessary.