Can't Truncate Table Befor Seeding

Question

I want to truncate my user table befor seed.i do like this :

DatabaseSeeder.php :

 <?php

 use Illuminate\Database\Seeder;
 use Illuminate\Support\Facades\DB;

 class DatabaseSeeder extends Seeder
 {
     public function run()
     {
         App\User::truncate();

         factory(App\User::class,1)->create();
     }
 }

Then run php artisan db:seed and have error:

In Connection.php line 664:

  SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constra
  int (`mr_musicer`.`dislikes`, CONSTRAINT `dislikes_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `mr_musicer`
  .`users` (`id`)) (SQL: truncate `users`)


In Connection.php line 458:

  SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constra
  int (`mr_musicer`.`dislikes`, CONSTRAINT `dislikes_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `mr_musicer`
  .`users` (`id`))

I want to now why i can't truncate my user table!

score 12 · Accepted Answer · answered Apr 01 '19 at 19:43

12

There is foreign key problem and table is trying to remind it to you. If you want to truncate table anyway.

    Schema::disableForeignKeyConstraints();

    // ... Some Truncate Query

    Schema::enableForeignKeyConstraints();

Don't forget To Use: use Illuminate\Support\Facades\Schema;

answered Apr 01 '19 at 19:43

Malkhazi Dartsmelidze

4,783
4
16
40

I made a package `snowbuilds/laravel-seeder-reset` that does exactly this, but it also makes truncating optional. Kind of handy if you just want to list the seeders and models to truncate. – wizardzeb Aug 13 '23 at 21:44

score 2 · Answer 2 · answered Jun 30 '22 at 04:47

2

Import DB:

use Illuminate\Support\Facades\DB;

And then use this piece of code to truncate the table:

DB::statement('SET FOREIGN_KEY_CHECKS=0;');
DB::table('posts')->truncate();
DB::statement('SET FOREIGN_KEY_CHECKS=1;');

answered Jun 30 '22 at 04:47

Mahsa

732
5
13

Hadayat Niazi · Answer 3 · 2022-07-21T08:35:00.847

1

The simplest way to truncate the data before seeding when you have foreign keys in the table

Just add this single line at the start of your run method.

Schema::disableForeignKeyConstraints();

DB::truncate('posts'); or Post::truncate();

Schema::enableForeignKeyConstraints();

edited Jul 21 '22 at 08:35

answered Jun 09 '21 at 18:02

Hadayat Niazi

1,991
3
16
28

1

I think it has a mistake, first you should use disableForeignKeyConstraints and after your operations call enableForeignKeyConstraints. – Omid Reza Heidari Jul 18 '22 at 09:46

score 0 · Answer 4 · answered Apr 01 '19 at 19:42

0

You have references to your users on some other table. You should add ->onDelete('cascade') to the dislikes table column that references user's id or delete all dislikes manually first.

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

answered Apr 01 '19 at 19:42

Ap Koponen

465
2
6

i used `cascade` in all of tables like this : `$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade')->onUpdate('cascade');` – Omid Reza Heidari Apr 01 '19 at 19:51

Can't Truncate Table Befor Seeding

4 Answers4