CODEWITHSUNDEEP
mysqllaravelforeign-keysmigration
Mohammad Reza Rezaei
Mohammad Reza Rezaei

Reputation: 7

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key

Hey guys I tried too much stuff and read some blogs or discussion I didn't fix my problem I'm new in laravel this project. I got error when I want to update a role on this error like

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or
update a child row: a foreign key constraint fails
(`shop`.`permission_role`, CONSTRAINT
`permission_role_permission_id_foreign` FOREIGN KEY (`permission_id`)
REFERENCES `permissions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
(SQL: insert into `permission_role` (`permission_id`, `role_id`)
values (9, 17))

my migration 

public function up()
{
    Schema::create('roles', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('title')->nullable();
        $table->timestamps();
    });
    Schema::create('permissions', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('title')->nullable();
        $table->timestamps();
    });

    Schema::create('role_user', function (Blueprint $table) {
        $table->integer('role_id')->unsigned();
        $table->integer('user_id')->unsigned();

        $table->foreign('role_id')
            ->references('id')
            ->on('roles')
            ->onDelete('cascade')
            ->onUpdate('cascade');

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

        $table->primary(['role_id','user_id']);
    });

    Schema::create('permission_role', function (Blueprint $table) {
        $table->integer('role_id')->unsigned();
        $table->integer('permission_id')->unsigned();


        $table->foreign('role_id')
            ->references('id')
            ->on('roles')
            ->onDelete('cascade')
            ->onUpdate('cascade');

        $table->foreign('permission_id')
            ->references('id')
            ->on('permissions')
            ->onDelete('cascade')
            ->onUpdate('cascade');
        $table->primary(['permission_id','role_id']);
    });


}

my controller

 public function store(Request $request)
{

    $role=Role::create([
        'name'=>$request['name'],
        'title'=>$request['title'],
    ]);
    $role->permissions()->sync($request->input(['permission_id']));
    return redirect(route('role.index'));

}

view:

<select name="permission_id[]" id="" multiple > 
  @foreach($permissions as $permission) 
    <option value="{{$permission->id}}"> {{$permission->title}} </option> 
  @endforeach 
</select>

Upvotes: 0

Views: 1600

Answers (1)

Watercayman
Watercayman

Reputation: 8178

Your migrations for your pivots are adding composite primary keys:

$table->primary(['role_id','user_id']);
$table->primary(['permission_id','role_id']);

This is not the same as adding foreign keys. You don't really need a primary key on those, but if you want one, I suggest you add it through the normal increments method:

$table->increments('id');

Try taking out those composite keys from the two pivot tables and re-run your migrations. Then try to create a user again and add roles to that user.

Upvotes: 1

Related Questions