Integrity constraint violation: 1452 Cannot add or update a child row in Laravel 5.2

Question

I get an error when i try to insert a role.I have found some solution of this issue in StackOverFlow but all those does not solve my problem.

I am trying to get id from roles table into roleID column of user_roles table.Here i am using Query Builder of Laravel 5.2.

public function store(Request $request)
{
    //
    $role = [];
    $role['role'] = $request->input('role');

    $data= Role::create($role);
    $id= $data->id;
    DB::table('user_roles')->insert([
    'roleID' => $id
    ]);
   //return $data;
    return redirect(route('allRole'));
}

When i insert any role then it insert new data in roles table but i am not getting roleID in user_roles table.I get an error:

 SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or
 update a child row: a foreign key constraint fails
 (`amarjobs`.`user_roles`, CONSTRAINT `fkuserRolesuserID` FOREIGN KEY
 (`userID`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE
 CASCADE) (SQL: insert into `user_roles` (`roleID`) values (14))

Where is the problem i have done? Thanks in advanced. Searched result:

1. First Solution. 2. Second Solution.

are you trying to assign roles to User ? and what package you are using ? — Qazi, Apr 20 '16 at 06:25
I did not used any package.I am trying to do make acl(access control list).Yes, now i am trying to roles for user. — Chonchol Mahmud, Apr 20 '16 at 06:34
can you mention you User, Role DB structure and model relationships ? So I can guide you accordingly — Qazi, Apr 20 '16 at 06:45
@ChoncholMahmud What is the db structure for `user_roles` table? I guess it has an `user_id` column & it has some foreign key constraint & here you aren't passing any `user_id` value `DB::table('user_roles')->insert([ 'roleID' => $id ]);` — ARIF MAHMUD RANA, Apr 20 '16 at 06:52
@ARIFMAHMUDRANA you can see the db structure here http://prntscr.com/auhfc3 — Chonchol Mahmud, Apr 20 '16 at 07:02

score 1 · Answer 1 · answered Apr 20 '16 at 07:12

1

It seems you are trying to insert only roleID to user_roles table. But within in your given error it seems the user_roles TABLE has also userID field what is foreign key and connect with users (id) table.

As user_roles is a pivot table and it has both roleID and userID and both are foreign key. So while inserting with only roleID value it's trying to insert the userID with the NULL value what is not matched with user Table any of IDs that's why Integrity constraint Violated.

So inserting data in a right way would be, you have to provide VALID userID as well.

$id= $data->id;
DB::table('user_roles')->insert([
'roleID' => $id,
'userID' => $EXISTING_USER_ID 
]);

I think, it will solve your problem.

answered Apr 20 '16 at 07:12

Emran

51
6

http://pastebin.com/03hdQEL6 ,In this way i am trying to get but i get an error ` ErrorException in RoleController.php line 50: Undefined property: Illuminate\Database\Eloquent\Collection::$id` – Chonchol Mahmud Apr 20 '16 at 08:19
I think, you understand my point perfectly.But how can i get `$EXISTING_USER_ID`. I have tried in first comment way.Please take a look. – Chonchol Mahmud Apr 20 '16 at 08:38
I have seen your pastebin code. you take all user by `$userdata = User::all(); $user_id = $userdata->id;` // This line should give error. // You have to take only one specific user and take its `id` attribute. Like `$userdata = User::all()->first(); $user_id = $userdata->id;` then try to insert data to `user_roles` table. And I mean via `$EXISTING_USER_ID` is any user ID which exist in `users` table. – Emran Apr 20 '16 at 09:29

ARIF MAHMUD RANA · Accepted Answer · 2016-04-20T09:16:08.370

As you can see your user_roles table required userID column & you aren't passing the field while inserting that's what generating the error.

DB::table('user_roles')->insert([
    'roleID' => $id,
    'userID' => $userId, //pass your userID here
]);

Also I like to mention couple of things you are naming your DB table column names in camel case which is bad you should be using underscores & as db sqls are generally case insensitive so using userID is equivalent to userid.

Also you are using DB::insert it will not insert any created_at or updated_at field or other eloquent features so I suggest you insert using eloquent model & using relations which is the beauty of laravel & eloquent active records

try like this

public function store(Request $request)
{
    //
    $role = [];
    $role['role'] = $request->input('role');

    $data= Role::create($role);
    $userdata = User::all();
    $user_id = $userdata->first()->id;
    $id= $data->id;
    DB::table('permissions')->insert([
    'role_id' => $id
    ]);
    DB::table('user_roles')->insert([
    'roleID' => $id,
    'userID' => $user_id
    ]);
   //return $data;
    return redirect(route('allRole'));
}

Here $userdata = User::all(); you are selecting all user do you want all your to have this role then you have to loop through on it & insert it. Or if you want first user to have this role then it's fine.

Also I suggest I think you should read more Laravels documentation to clear things.

http://pastebin.com/03hdQEL6 ,In this way i am trying to get but i get an error ` ErrorException in RoleController.php line 50: Undefined property: Illuminate\Database\Eloquent\Collection::$id` — Chonchol Mahmud, Apr 20 '16 at 08:19

score 0 · Answer 3 · answered Apr 20 '16 at 07:07

0

in your above error, you are missing the userID field, which is must in relationship perspective. so you should pass the userID then your code will be working.

$role = [];
$role['role'] = $request->input('role');

$data= Role::create($role);
$id= $data->id;
DB::table('user_roles')->insert([
'roleID' => $id,
'userID'=>1, // pass userID with whom you wan to attach new roleID
]);

return redirect(route('allRole'));

here is advance version of using relationship method

Add these method in respective models

user model

public function userRoles(){
    return $this->belongsToMany('App\Role','user_roles','userID','roleID');

}

role model

public function roleUsers(){
    return $this->belongsToMany('App\User','user_roles','roleID', 'userID');

}

and do this for insertion of roles.

$user = App\User::find(1);    
$user->roles()->attach($roleId);

$role = [];
$role['role'] = $request->input('role');

$data= Role::create($role);
$id= $data->id;

$user->userRoles()->attach(['
  'roleID' => $id,
  'userID'=>$user->id
']);

return redirect(route('allRole'));

answered Apr 20 '16 at 07:07

Qazi

5,015
8
42
62

When i am trying to add roles i get error says `Call to a member function roles() on a non-object`.I am puttitng bottom code in RoleController. – Chonchol Mahmud Apr 20 '16 at 08:03
comment out or remove this line `$user->roles()->attach($roleId);` – Qazi Apr 20 '16 at 08:29
Now its shows `Class 'App\Http\Controllers\App\User' not found`.One things why you use App\User? in attach([ 'roleID' => $id, 'userID'=>$user->id ]); it will be this you forget to remove single quote between [] – Chonchol Mahmud Apr 20 '16 at 08:35
`App\User` is your `User` model. did you added above method in your User and Role Model ? – Qazi Apr 20 '16 at 08:39
Yes i added `use App/User;` in Role model.But i think , it will be `$user = User::find(1);` Am i right? – Chonchol Mahmud Apr 20 '16 at 08:41
if you have written this on top of your class/controller `use App/User;` then you can use this `$user = User::find(1);` – Qazi Apr 20 '16 at 09:19

Integrity constraint violation: 1452 Cannot add or update a child row in Laravel 5.2

3 Answers3