Skip to content

Instantly share code, notes, and snippets.

@isimmons
Last active March 15, 2024 10:47
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.

@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.

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