How to Solve Foreign Key Constraint Error when Running Truncate Command in Laravel Seeder
When we develop an application, most of the time we need to test how it interact with database. In Laravel, we are using database seeder to seed the database with some initial data. Using Laravel's built-in database seeder is very easy.
If we want to wipe all database tables, we can use this command:
$ php artisan migrate:fresh
If we need to run all database seeders soon after the database being wiped out, we can use this command:
$ php artisan migrate:fresh --seed
But there's a case when:
- We have some data other than the seed and we don't want to lose it.
- We want to run specific table seeder.
For
example, I have a model called NavigationMenu. This model has many
translations, defined in NavigationMenuTranslation model. Everytime NavigationMenu
model being truncated, NavigationMenuTranslation
model
should also being truncated. This way we shouldn't have orphaned
records in our database.
So, here's how I defined my NavigationMenuSeeder
class like below:
// ... snipped
public function run()
{
NavigationMenu::truncate();
NavigationMenuTranslation::truncate();// ... snipped
But when running that seeder with this command:
$ php artisan migrate --class=NavigationMenuSeeder
The system responds with something like below:
... snipped
SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (`laravel`.`navigation_menu_translations`, CONSTRAINT `navigation_menu_translations_navigation_menu_id_foreign` FOREIGN KEY (`navigation_menu_id`) REFERENCES `
laravel
`.`naviga) (SQL: truncate table `navigation_menus`)
... snipped
If you understand how the database works, this should be pretty straightforward. The main issue is we have foreign key constraints in our tables. It is expected and designed like that to keep our database integrity.
There are some workarounds for this issue.
Disable Foreign Key Constraints
If
you're sure that your related tables could be truncated, we could
disable foreign key constraints temporarily. This could be done using
Laravel's Schema facade:
Schema::disableForeignKeyConstraints();
Schema::enableForeignKeyConstraints();
So, using our example above, here's our new code:
// ... snipped
public function run()
use Illuminate\Support\Facades\Schema;
class NavigationMenuSeeder extends Seeder
{
{
Schema::disableForeignKeyConstraints();
NavigationMenu::truncate();
NavigationMenuTranslation::truncate();
Schema::enableForeignKeyConstraints();// ... snipped
Now we can run our seeder as usual.
Use delete() Method
If you're not sure about your relationship or you're working on a live system, use delete()
method.
class NavigationMenuSeeder extends Seeder
public function run()
{
{
NavigationMenu::query()->delete();// ... snipped
Final Words
I hope that you now know how to solve foreign key constraint error when running truncate command in Laravel seeder. If you run into any issues or have any feedback feel free to drop a comment below.