Laravel 5.1 Migration and Seeding Cannot truncate a table referenced in a foreign key constraint

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');
}

It's trying to seed the database on the first migration because you are passing the `--seed` option. Your migrations are fine, it's the seeding which is the problem. Can you add the code for the vehicles table seeder? — user1669496, Jul 02 '15 at 21:16
You may also want to try `delete()` rather than `truncate()`. That should work. — user1669496, Jul 02 '15 at 21:24
Hi @user3158900, I found it in DatabaseSeeder. I've added added delete() instead and it works. If you post as answer I'll mark it as correct. — mtpultz, Jul 02 '15 at 22:14

score 73 · Answer 1 · edited Apr 26 '19 at 15:22

73

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

And it works！

edited Apr 26 '19 at 15:22

molerat

946
4
15
43

answered Feb 23 '16 at 07:30

haobird

847
6
3

4

Skipping foreign key checking is a very bad practice. Truncating resets the auto-incrementing ID for example, so your relations will be wrong from this point onward – CommonToast Dec 16 '16 at 10:02
@CommonToast I agree with you, when talking about real world data, that it's a bad practice, but in a greenfield project, this might help when creating migrations and seeds. How do you fix orphaned data, with full restrictions, if these might have occurred in a large and multiple scheme environment? – Semo Feb 21 '17 at 06:55

score 48 · Accepted Answer · answered Jul 06 '15 at 12:43

48

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

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

answered Jul 06 '15 at 12:43

user1669496

32,176
9
73
65

score 20 · Answer 3 · answered Mar 29 '20 at 11:47

20

before drop

Schema::disableForeignKeyConstraints();

and before close run method

Schema::enableForeignKeyConstraints();

answered Mar 29 '20 at 11:47

AhmedSeaf

221
2
3

best answer here – Jannie Theunissen Jun 17 '20 at 18:49

score 8 · Answer 4 · answered May 30 '17 at 04:04

8

To clear a table using Eloquent:

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

Example using default user model

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

answered May 30 '17 at 04:04

Rob

6,758
4
46
51

Kolawole Emmanuel Izzy · Answer 5 · 2021-01-06T22:54:34.433

I faced the same issue with my Role and Permission setup and this is what I did that worked as I wanted. Truncate() will reset the Increment column to 1 but throw a foreign key error while delete on the other hand works fine but doesn't reset the increment column, so I did the following in my Seeder's file (i.e RoleSeeder.php in my case)

1. [ delete() method ]

$roles = [];

... // Some foreach statement to prepare an array of data for DB insert()

// Delete and Reset Table
DB::table('roles')->delete();
DB::statement("ALTER TABLE `roles` AUTO_INCREMENT = 1");
// Insert into table
DB::table('roles')->insert($roles);

This will cascade all other child tables attached to the roles table. in my case users_roles table. This way I avoided disabling and enabling foreign key checks.

2. Something to put in mind / Second Approach [ truncate() method ]

if you don't have the intention of deleting all the data stored in the child's table (in my case users_roles table) ... You can go with truncate() and then in the DatabaseSeeders.php file you disable and enable foreign key check. As I tested this and the users_roles data was intact, the seed on affected roles table.

//RoleSeeders.php File

$roles = [];

... // Some foreach statement to prepare an array of data for DB insert()

// Truncate Table
DB::table('roles')->truncate();
// Insert into table
DB::table('roles')->insert($roles);

Then in the DatabaseSeeder.php file, you do;

public function run()
{
    DB::statement('SET FOREIGN_KEY_CHECKS=0;');

    $this->call([
        RoleSeeder::class,
    ]);

    DB::statement('SET FOREIGN_KEY_CHECKS=1;');
}

But I prefer the delete() method, since I don't have to disable/enable the foreign key check

score 3 · Answer 6 · answered Dec 06 '17 at 15:15

3

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.

answered Dec 06 '17 at 15:15

Lilian Sun

51
4

score 0 · Answer 7 · edited Dec 06 '17 at 15:04

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.

score 0 · Answer 8 · answered Feb 28 '20 at 06:36

0

You could just drop it with.

$table->dropForeign('posts_user_id_foreign');

answered Feb 28 '20 at 06:36

Nicholas English

1

score 0 · Answer 9 · answered Apr 13 '20 at 00:15

I'm using Laravel 7.x, this worked for me. Goes without saying that it should only be used in development. To read more, check it out here.

DatabaseSeeder.php

    <?php

use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    /**
     * Seed the application's database.
     *
     * @return void
     */
    public function run()
    {
        // the Eloquent part and disabling and enabling of foreign keys is only intended for development
        Eloquent::unguard();

        //disable foreign key check for this connection before running seeders
        DB::statement('SET FOREIGN_KEY_CHECKS=0;');

        $this->call(RolesTableSeeder::class);
        $this->call(UsersTableSeeder::class);

        // 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;');
    }
}

Laravel 5.1 Migration and Seeding Cannot truncate a table referenced in a foreign key constraint

9 Answers9

Linked