Truncate tables with foreign key constraints in a Laravel seed file.

gistfile1.md

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.

@mikedamoiseau Thanks sir this is works great!

Thank you!

Great man!

Thank you!

thank you bro!

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 

Thanks!
It works for me!

thx! works for me too

Thx this saved my project

@mikedamoiseau thank you!!

Thanks for this fix. works on laravel 7

you can use this code on Laravel 7

Schema::disableForeignKeyConstraints();

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

Schema::enableForeignKeyConstraints();

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.

thanks man, it worked for me!

j

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.

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