I tried seeding my seeder file but I get this error

Question

I tried to issue

$  php artisan db:seed

Seeding: RoleSeeder

In Connection.php line 664:

SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (news.users, CONST RAINT users_role_id_foreign FOREIGN KEY (role_id) REFERENCES news.roles (id)) (SQL: truncate roles)

This is my Roleseeder.php file

<?php

use Illuminate\Database\Seeder;

use App\Role ;

class RoleSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {

        Role::truncate();

        Role::create([
            'title'   => 'Students',
            'description' => 'Lorem ipsum dolor.'
        ]);

   // ....

I don't understand why I get that error ..

I have this migration file

Schema::table('users', function (Blueprint $table) {
    $table->integer('role_id')->unsigned()->after("id")->nullable();
    $table->foreign('role_id')
            ->references('id')->on('roles')
            ->onDelete('restrict');
});

also you cannot truncate it because keys of the table exist in your news table, the error is pretty self explainatory — sietse85, Apr 23 '19 at 11:53
look at [this](https://stackoverflow.com/questions/31192207/laravel-5-1-migration-and-seeding-cannot-truncate-a-table-referenced-in-a-foreig) — xmaster, Apr 23 '19 at 11:54

Qirel · Answer 1 · 2019-04-23T12:00:04.680

1

Your seeding fails because you are attempting to truncate a table where there are values that other tables depend on through a foreign-key relation. Your users has a role, which restricts you from truncating the roles table.

Instead, you can use the firstOrCreate() method, which will create the value only if it does not exist.

public function run()
{
    Role::firstOrCreate([
            'title'   => 'Students',
            'description' => 'Lorem ipsum dolor.'
        ]);
}

Alternatively, if you want to have the ability to change the description between each seeding for some reason, you can use firstOrNew().

public function run()
{
    $role = Role::firstOrNew([
            'title'   => 'Students'
        ]);
    $role->description = 'Lorem ipsum dolor.';
    $role->save();
}

Alternatively, you can change your foreign-key relation to set the value to null if it gets deleted (note that when you then truncate your Roles, all users will have their roles set to NULL).

->onDelete('set null');

If you actually need to truncate the table, you will first need to clear out the depending values in your roles table.

edited Apr 23 '19 at 12:00

answered Apr 23 '19 at 11:54

Qirel

25,449
7
45
62

How about restrict ? does restrict like affect the truncating ? – TheBAST Apr 23 '19 at 12:04
Because you have `restrict`, and there are values in `roles` that are referenced in `users`, it cannot be deleted. That's what it means - restrict deleting if there are values in other tables that depend on it. – Qirel Apr 23 '19 at 12:10
The cause is slightly different: in MySQL the truncate table statement drops and recreates the table in the background. If a table is referenced by a foreign key, then it cannot be dropped (regardless whether there are any referenced values in it, so you get the error message even if the table is empty), hence the error message. So, changing the foreign key to cascade deletes will not help in case of a truncate. You have to use a delete statement instead or drop the fk, truncate and re-create the fk. – Shadow Apr 23 '19 at 12:42

I tried seeding my seeder file but I get this error

1 Answers1