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

Question

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.

I'd suggest looking at https://stackoverflow.com/questions/5452760/how-to-truncate-a-foreign-key-constrained-table -- You'll need to use a `DB::statement()` to disable the foreign keys — aynber, Feb 14 '23 at 19:32

score 0 · Answer 1 · answered Feb 15 '23 at 03:47

use it like this

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

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

class BarSeeder extends Seeder
{
    public function run()
    {
       

        \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::create([
            'qux_name' => 'Qux',
            'bar_id' => 1,
        ]);

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

please use this hierarchy level because you've the foreign keys and when you're going to truncate it cannot find the reference one Hope it helps

score 0 · Accepted Answer · answered Mar 21 '23 at 20:38

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,
        ]);
    }
}

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

2 Answers2