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.

@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