Laravel 5.1 Migration and Seeding Cannot truncate

Question

I'm trying to run the migration (see below) and seed the database, but when I run

php artisan migrate --seed

I get this error:

Migration table created successfully.
Migrated: 2015_06_17_100000_create_users_table
Migrated: 2015_06_17_200000_create_password_resets_table
Migrated: 2015_06_17_300000_create_vehicles_table

[Illuminate\Database\QueryException]
SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table
referenced in a foreign key constraint (`app`.`vehicles`, CONSTRAINT `vehic
les_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `app`.`users` (`id`
)) (SQL: truncate `users`)

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table
referenced in a foreign key constraint (`app`.`vehicles`, CONSTRAINT `vehic
les_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `app`.`users` (`id`
))

I looked up what this error is supposed to mean, and also found examples of other people running into the same problem, even just related to using MySQL, and their solutions, but applying:

DB::statement('SET FOREIGN_KEY_CHECKS=0;'); and 
DB::statement('SET FOREIGN_KEY_CHECKS=1;'); 

Within down() doesn't seem to work and when I run describe in MySQL the tables look right.

The migrations are named properly to make sure the users table is migrated first, and then vehicles so the foreign key can be applied, and the tables being setup up correctly suggests the migrations were run, but then the error occurs. I dropped and recreated the DB and tried it again and it is the same result. I also don't understand why it is trying to truncate on the first migration and seed of the database, I wouldn't have thought that would occur when you tried to run php artisan migrate:refresh --seed.

// 2015_06_17_100000_create_users_table.php

class CreateUsersTable extends Migration
{
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('username', 60)->unique();
            $table->string('email', 200)->unique();
            $table->string('password', 255);
            $table->string('role')->default('user');
            $table->rememberToken();
            $table->timestamps();
        });
    }
}

public function down()
{
    Schema::drop('users');
}

// 2015_06_17_300000_create_vehicles_table.php

class CreateVehiclesTable extends Migration
{
    public function up()
    {
        Schema::create('vehicles', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('user_id')->unsigned();
            $table->string('make');
            $table->string('model');
            $table->string('year');
            $table->string('color');
            $table->string('plate');
            $table->timestamps();

            $table->foreign('user_id')->references('id')->on('users');
        });
    }
}

public function down()
{
    Schema::drop('vehicles');
}

Answer 1

DB::statement('SET FOREIGN_KEY_CHECKS=0;');
App\User::truncate();
DB::statement('SET FOREIGN_KEY_CHECKS=1;');

And it works！

Answer 2

you can use

DB::table('your_table_name')->delete();

to empty a table, this won't delete the table structure. But the auto increment id will not start from initial number.

Answer 3

Here is what works for me every time. When you're adding the foreign key, make sure to add cascade. the syntax is like this

$table->foreign('column')->references('id')->on('table_name')->onDelete('cascade');

Make sure to replace id with whatever field is applicable for you.

Now before running the seeding add this instead of trucate

DB::statement('DELETE FROM table_name');

It will delete all the data. Hope this helps.

Answer 4

As the error says, you can not truncate tables referenced by foreign keys. Delete should work though...

DB::table('some_table')->delete();

Answer 5

To clear a table using Eloquent:

Model::query()->delete();

Example using default user model

User::query()->delete();