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

[robertnicjoo]

When I try to seed my roles and permissions I am getting this error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`myapp`.`role_has_permissions`, CONSTRAINT `role_has_permissions_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE) (SQL: insert into `role_has_permissions` (`permission_id`, `role_id`) values (512, 0))

What I've done

1. followed every step here https://spatie.be/docs/laravel-permission/v3/advanced-usage/uuid
2. Tested manually insert data into role_has_permissions table and it was working (accepting uuid's)
3. Tested migration with both scenario 'model_morph_key' => 'model_id', and 'model_morph_key' => 'model_uuid',

Code

1
seeder

<?php

namespace Database\Seeders;

use Illuminate\Database\Seeder;
use Spatie\Permission\Models\Role;
use Spatie\Permission\Models\Permission;
use DB;

class UserRolesTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        // Reset cached roles and permissions
        app()[\Spatie\Permission\PermissionRegistrar::class]->forgetCachedPermissions();

        // create permissions
        Permission::create(['id' => \Ramsey\Uuid\Uuid::uuid4()->toString(), 'name' => 'add document']);
        Permission::create(['id' => \Ramsey\Uuid\Uuid::uuid4()->toString(), 'name' => 'edit document']);
        Permission::create(['id' => \Ramsey\Uuid\Uuid::uuid4()->toString(), 'name' => 'download document']);
        Permission::create(['id' => \Ramsey\Uuid\Uuid::uuid4()->toString(), 'name' => 'add user']);
        Permission::create(['id' => \Ramsey\Uuid\Uuid::uuid4()->toString(), 'name' => 'edit user']);
        Permission::create(['id' => \Ramsey\Uuid\Uuid::uuid4()->toString(), 'name' => 'delete user']);

        // this can be done as separate statements
        $role = Role::create(['id' => \Ramsey\Uuid\Uuid::uuid4()->toString(), 'name' => 'user']);
        $role->givePermissionTo([
            'add document',
            'edit document',
            'download document',
        ]);

        $role = Role::create(['id' => \Ramsey\Uuid\Uuid::uuid4()->toString(), 'name' => 'staff'])
            ->givePermissionTo([
                'add document',
                'edit document',
                'download document',
                'add user',
                'edit user',
                'delete user',
            ]);
        $role = Role::create(['id' => \Ramsey\Uuid\Uuid::uuid4()->toString(), 'name' => 'admin']);
        $role->givePermissionTo(Permission::all());
    }
}

2

Adding models

// Roles
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use GoldSpecDigital\LaravelEloquentUUID\Database\Eloquent\Uuid;


class Role extends Model
{
    use Uuid;
    protected $keyType = 'string';
    public $incrementing = false;

    public function user(){
        return $this->belongsTo(User::class);
    }

    public function permission(){
        return $this->hasMany(Permission::class);
    }
}

// Permissions
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use GoldSpecDigital\LaravelEloquentUUID\Database\Eloquent\Uuid;


class Permission extends Model
{
    use Uuid;
    protected $keyType = 'string';
    public $incrementing = false;

    public function role(){
        return $this->belongsTo(Role::class);
    }
}

Any idea why I still get migrate error while table columns are accepting UUID?

[drbyte]

The SQL error suggests that you have a 0 value for a permission id in the pivot table. This suggests that it received a string when trying to insert what was expected to be a number when the pivot record was created.

Why do your Role and Permission models not extend the Spatie model? or at least implement the contract?
https://spatie.be/docs/laravel-permission/v3/advanced-usage/extending#extending-role-and-permission-models

[robertnicjoo]

@drbyte I had a work around for this,

I let role and permission tables to use default increment ids (as I don't have that many roles or permissions) but I've changed model_id columns to accept uuid because my users table using uuid so now it works :)

Thanks.

[rezadaulay]

You can change role & permission using UUID, following this steps:

1. Please following this steps to change all migrations of role & permission ID to UUID: https://spatie.be/docs/laravel-permission/v5/advanced-usage/uuid
2. Create new Role model

<?php
namespace App\Models;
use Spatie\Permission\Models\Role as SpatieRole;
use App\Traits\Uuid;

class Role extends SpatieRole
{
    use Uuid;
}

3. Change config/permission.php:

'models' => [

        .....

        'role' => App\Models\Role::class,

 ]

Optional (You can use your own UUID trait):

<?php

namespace App\Traits;

use Illuminate\Support\Str;

trait Uuid
{
    protected static function boot()
    {
        parent::boot();

        static::creating(function ($model) {
            $model->keyType = 'string';
            $model->incrementing = false;

            $model->{$model->getKeyName()} = $model->{$model->getKeyName()} ?: (string) Str::orderedUuid();
        });
    }

    public function getIncrementing()
    {
        return false;
    }
    
    public function getKeyType()
    {
        return 'string';
    }
}

Now you can use uuid on your Role model, you can do same thing with Permission model.