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
use Illuminate\Support\Facades\Schema;

class NavigationMenuSeeder extends Seeder
{
    public function run()
    {
        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.