phplaraveleloquent-relationshiplaravel-migrationslaravel-seeding

Laravel Seeder throws SQLSTATE[42000]: Syntax error or access violation


I am having the following db tables

// Table 1: Foos
id, foo_name, foo_type, created_at, updated_at

// Table 2: Bars
id, bar_name, bar_type, parent_id, foo_id [ForeignKey], created_at, updated_at

// Table 3: Quxes
id, qux_name, bar_id [ForeignKey], created_at, updated_at

And I am having the following seeders setup

class FooSeeder extends Seeder
{
    public function run()
    {
        \App\Models\Qux::truncate();
        \App\Models\Bar::truncate();
        \App\Models\Foo::truncate();

        \App\Models\Foo::create([
            'foo_name' => 'Foo',
            'foo_type' => 'Foo type',
        ]);
    }
}

class BarSeeder extends Seeder
{
    public function run()
    {
        \App\Models\Qux::truncate();
        \App\Models\Bar::truncate();

        \App\Models\Bar::create([
            'bar_name' => 'Bar',
            'bar_type' => 'Bar type',
            'foo_id' => 1,
            'parent_id' => 1,
        ]);

        \App\Models\Bar::create([
            'bar_name' => 'Bar Bar',
            'bar_type' => 'Bar Bar type',
            'foo_id' => 1,
            'parent_id' => 0,
        ]);
    }
}


class QuxSeeder extends Seeder
{
    public function run()
    {
        \App\Models\Qux::truncate();
        \App\Models\Bar::truncate();

        \App\Models\Qux::create([
            'qux_name' => 'Qux',
            'bar_id' => 1,
        ]);

        \App\Models\Qux::create([
            'qux_name' => 'Qux Qux',
            'bar_id' => 1,
        ]);
    }
}

When I try to run php artisan db:seed I get the following error

SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (`mylaravelschema`.`quxes`, CONSTRAINT `qux_bar_id_foreign` FOREIGN KEY (`bar_id`) REFERENCES `mylaravelschema`.`bars` (`id`)) (SQL: truncate table `bars`)

I have been trying to play with the order of truncating the tables on these three seeders and still haven't manage to sort this, any help appreciated.


Solution

  • I ended up using this solution

    class DatabaseSeeder extends Seeder
    {
        protected $tables = [
            'foos',
            'bars',
            'quxes',
        ];
    
        public function run()
        {
            DB::statement('SET FOREIGN_KEY_CHECKS = 0');
    
            foreach ($this->tables as $table) {
                DB::table($table)->truncate();
            }
    
            DB::statement('SET FOREIGN_KEY_CHECKS = 1');
    
            $this->call([
                FooSeeder::class,
                BarSeeder::class,
                QuxSeeder::class,
            ]);
        }
    }