Question 15808 in Laravel Question posted in
The official Laravel Documentation can be found here.

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()
    {
        AppModelsQux::truncate();
        AppModelsBar::truncate();
        AppModelsFoo::truncate();

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

class BarSeeder extends Seeder
{
    public function run()
    {
        AppModelsQux::truncate();
        AppModelsBar::truncate();

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

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


class QuxSeeder extends Seeder
{
    public function run()
    {
        AppModelsQux::truncate();
        AppModelsBar::truncate();

        AppModelsQux::create([
            'qux_name' => 'Qux',
            'bar_id' => 1,
        ]);

        AppModelsQux::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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

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

  2. 0

    use it like this

    class FooSeeder extends Seeder
{
    public function run()
    {
        AppModelsFoo::truncate();
        AppModelsBar::truncate();
        AppModelsQux::truncate();
        

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

class BarSeeder extends Seeder
{
    public function run()
    {
       

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

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


class QuxSeeder extends Seeder
{
    public function run()
    {

        AppModelsQux::create([
            'qux_name' => 'Qux',
            'bar_id' => 1,
        ]);

        AppModelsQux::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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search