Skip to content

Instantly share code, notes, and snippets.

@isimmons
Last active July 26, 2024 16:15
Show Gist options
  • Save isimmons/8202227 to your computer and use it in GitHub Desktop.
Save isimmons/8202227 to your computer and use it in GitHub Desktop.
Truncate tables with foreign key constraints in a Laravel seed file.

For the scenario, imagine posts has a foreign key user_id referencing users.id

public function up()
{
	Schema::create('posts', function(Blueprint $table) {
		$table->increments('id');
		$table->string('title');
		$table->text('body');
		$table->integer('user_id')->unsigned();
		$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
		$table->timestamps();
	});
}

running seeds with truncate() will trigger a constraint violation if foreign key checks is not disabled.

	Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint ...

The fix:

DatabaseSeeder.php

class DatabaseSeeder extends Seeder {

	/**
	 * Run the database seeds.
 	 *
	 * @return void
	 */
	public function run()
	{
		Eloquent::unguard();

		//disable foreign key check for this connection before running seeders
		DB::statement('SET FOREIGN_KEY_CHECKS=0;');
			
		$this->call('UsersTableSeeder');
		$this->call('PostsTableSeeder');

		// supposed to only apply to a single connection and reset it's self
		// but I like to explicitly undo what I've done for clarity
		DB::statement('SET FOREIGN_KEY_CHECKS=1;');
		
	}

}

fix from here http://stackoverflow.com/questions/14666277/laravel-4-working-with-relationships-in-seeds

As pointed out in the comments at SO you wouldn't want to disable foreign key checks on a production app but you really don't want to run seed files in production either so obviously this is not intended for production but helpful in development.

@ezequiel9
Copy link

Great man!

@micessien
Copy link

Thank you!

@yunusemremert
Copy link

thank you bro!

@M-Abdullahi
Copy link

M-Abdullahi commented Apr 16, 2019

Another option would be to use the schema facade to disable the fk constraint.

``
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::disableForeignKeyConstraints();
Schema::dropIfExists('organisation_addresses');
Schema::enableForeignKeyConstraints();
}


I was also thinking on the idea of creating 2 separate migrations; 
one to disable and another one to enable the fk constraints. 

You can edit the timestamp naming part and place the fk enabling migration to 
run as the _first_ migration and the disabling one to run _last_ after  all other migrations have run. 

~  a thought that i haven't tried, yet.

FYI: I use mysql and do this on development environment 

@MutlaqAldhbuiub
Copy link

Thanks!
It works for me!

@yunuselci
Copy link

thx! works for me too

@Amvos2000
Copy link

Thx this saved my project

@RyotaBannai
Copy link

@mikedamoiseau thank you!!

@edrisaturay
Copy link

Thanks for this fix. works on laravel 7

@pishguy
Copy link

pishguy commented May 29, 2020

you can use this code on Laravel 7

Schema::disableForeignKeyConstraints();

$this->call(UserTableSeeder::class);
$this->call(LanguageTableSeeder::class);

Schema::enableForeignKeyConstraints();

@henryavila
Copy link

Hi, I have this problem in MsSQL, and I conclude that I have just 2 options (beside moving away from MSSQL =D)

  1. Remove the foreign key, truncate and recreate the foreign key
  2. Delete table data and reset the Auto increment

I Found the approach 2 much better. Here is the working code:

$tableName = (new Model())->getTable();
\Illuminate\Support\Facades\DB::table($tableName )->delete();
\Illuminate\Support\Facades\DB::statement("DBCC CHECKIDENT ({$tableName }, RESEED, 0)");

Baiscally its what the truncate does (obviously the truncate is much faster), But it work.

@remusbejusca87
Copy link

thanks man, it worked for me!

@pankajToday
Copy link

j

@kevincittadini
Copy link

This code seems to work too (MySQL, Laravel 5.2.45):

Schema::disableForeignKeyConstraints();
DB::table('patients')->truncate();
Schema::enableForeignKeyConstraints();

This works for whatever DB you're using.
I'm switching from MySQL to Postgres and this specific were amazing.

Thanks.

@keneil2
Copy link

keneil2 commented Jul 26, 2024

This code seems to work too (MySQL, Laravel 5.2.45):

Schema::disableForeignKeyConstraints();
DB::table('patients')->truncate();
Schema::enableForeignKeyConstraints();

thanks man this worked like a charm

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment