Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save dansup/d4c563c5577d034bf024e6c79d7d0b35 to your computer and use it in GitHub Desktop.
Save dansup/d4c563c5577d034bf024e6c79d7d0b35 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.

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