Use UUID for all of the tables, but assignRole to fail.

[carveybunt]

I'm using Laravel v10.2 and permission v5.91.

When I assign a role to a user, I got an error as follows:

  SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`xuehai_databases`.`model_has_roles`, CONSTRAINT `model_has_roles_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE) (Connection: mysql, SQL: insert into `model_has_roles` (`department_id`, `model_type`, `model_uuid`, `role_id`) values (98b9bbc3-0075-4917-be28-11901a788376, App\Models\User, 98b9bbc0-9865-407e-85b9-443653ff3404, 98))

It seems role_id is int, not uuid, why?

My assign code is as follows:

$superUser = User::where("email", "xxx@xxx.com")->first();
$superUser->assignRole("super admin"); // The "super admin" role is already inserted into the role table.

Migrate is as follows:

Schema::create($tableNames["permissions"], function (Blueprint $table) {
            // $table->bigIncrements("id"); // permission id
            $table->uuid("id")->primary(); // permission id change to uuid
……

Schema::create($tableNames["roles"], function (Blueprint $table) use (
    $teams,
    $columnNames
) {
    // $table->bigIncrements("id"); // role id
    $table->uuid("id")->primary(); // role id change to uuid

My config is as follows:

"model_morph_key" => "model_uuid",
……
"team_foreign_key" => "department_id",
……
"teams" => true,

My extended model is as follows:

class User extends Authenticatable
{
    use HasApiTokens, HasFactory, Notifiable, HasUuids, HasRoles;

class Role extends SpatieRole
{
    use HasUuids;
……

class Permission extends SpatiePermission
{
    use HasUuids;

class Department extends Model
{
    use HasFactory, HasUuids;

Do others have similar problems? Someone could tell me how to fix this problem, please?

[drbyte]

You SQL error statement shows the SQL it's trying to run, and it shows that the role_id trying to be INSERTED is 98.

Is that the actual role_id of the "super admin" role that you said is already inserted into the database?
How was that role inserted?

[carveybunt]

"super admin" inserted by the Role model, and the "department_id" of "super admin" is null. I can see "super admin" information in the role table of MySQL, and the 'id' column actually is uuid. I used UUID for all of the tables of MySQL and use the HasUuids trait of Laravel.

[parallels999]

Did you change to UUID pivots on migration?
model_has_permissions, model_has_roles, role_has_permissions
Look at tests

laravel-permission/tests/TestCase.php

Lines 162 to 164 in a769221

	'bigIncrements',
	'unsignedBigInteger($pivotRole)',
	'unsignedBigInteger($pivotPermission)',

laravel-permission/tests/TestCase.php

Lines 172 to 174 in a769221

	'uuid',
	'uuid($pivotRole)->nullable(false)',
	'uuid($pivotPermission)->nullable(false)',

You can use custom pivot names

laravel-permission/config/permission.php

Lines 78 to 79 in a769221

	'role_pivot_key' => null, //default 'role_id',
	'permission_pivot_key' => null, //default 'permission_id',

[carveybunt]

I already did that. I thought that is not the uuid reason but the team feature to cause the error.

My migration is as follows:

       Schema::create($tableNames["permissions"], function (Blueprint $table) {
            // $table->bigIncrements("id"); // permission id
            $table->uuid("id")->primary(); // permission id change to uuid
            $table->string("name"); // For MySQL 8.0 use string('name', 125);
            $table->uuid("parent_id")->nullable();
            $table->string("title"); // my add
            $table->tinyInteger("sort")->default(10); // my add
            $table->string("guard_name"); // For MySQL 8.0 use string('guard_name', 125);
            $table->timestamps();
            $table->unique(["name", "guard_name", "parent_id"]);
        });

       Schema::create($tableNames["roles"], function (Blueprint $table) use (
            $teams,
            $columnNames
        ) {
            // $table->bigIncrements("id"); // role id
            $table->uuid("id")->primary(); // role id change to uuid
            if ($teams || config("permission.testing")) {
                // permission.testing is a fix for sqlite testing
                $table
                    // ->unsignedBigInteger($columnNames["team_foreign_key"])
                    ->foreignUuid($columnNames["team_foreign_key"])
                    ->nullable()
                    ->constrained("departments")
                    ->onDelete("restrict");
                $table->index(
                    $columnNames["team_foreign_key"],
                    "roles_team_foreign_key_index"
                );
            }
            $table->string("name"); // For MySQL 8.0 use string('name', 125);
            $table->string("title"); // my add
            $table->tinyInteger("sort")->default(10); // my add
            $table->string("guard_name"); // For MySQL 8.0 use string('guard_name', 125);
            $table->timestamps();
            if ($teams || config("permission.testing")) {
                $table->unique([
                    $columnNames["team_foreign_key"],
                    "name",
                    "guard_name",
                ]);
            } else {
                // $table->unique(["name", "guard_name"]);
                $table->unique(["name", "guard_name", "department_id"]); // my change
            }
       });

       Schema::create($tableNames["model_has_permissions"], function (
            Blueprint $table
        ) use ($tableNames, $columnNames, $teams) {
            // $table->unsignedBigInteger(PermissionRegistrar::$pivotPermission);
            $table->uuid(PermissionRegistrar::$pivotPermission)->index(); // change uuid

            $table->string("model_type");
            // $table->unsignedBigInteger($columnNames["model_morph_key"]);
            $table->uuid($columnNames["model_morph_key"]); // my change
            $table->index(
                [$columnNames["model_morph_key"], "model_type"],
                "model_has_permissions_model_id_model_type_index"
            );

            $table
                ->foreign(PermissionRegistrar::$pivotPermission)
                // ->foreignUuid(PermissionRegistrar::$pivotPermission)
                ->references("id") // permission id
                ->on($tableNames["permissions"])
                ->onDelete("cascade");
            if ($teams) {
                // $table->unsignedBigInteger($columnNames["team_foreign_key"]);
                $table->uuid($columnNames["team_foreign_key"]);

                $table->index(
                    $columnNames["team_foreign_key"],
                    "model_has_permissions_team_foreign_key_index"
                );

                $table->primary(
                    [
                        $columnNames["team_foreign_key"],
                        PermissionRegistrar::$pivotPermission,
                        $columnNames["model_morph_key"],
                        "model_type",
                    ],
                    "model_has_permissions_permission_model_type_primary"
                );
            } else {
                $table->primary(
                    [
                        PermissionRegistrar::$pivotPermission,
                        $columnNames["model_morph_key"],
                        "model_type",
                    ],
                    "model_has_permissions_permission_model_type_primary"
                );
            }
        });

       Schema::create($tableNames["model_has_roles"], function (
            Blueprint $table
        ) use ($tableNames, $columnNames, $teams) {
            // $table->unsignedBigInteger(PermissionRegistrar::$pivotRole);
            $table->uuid(PermissionRegistrar::$pivotRole); // change uuid

            $table->string("model_type");
            // $table->unsignedBigInteger($columnNames["model_morph_key"]);
            $table->uuid($columnNames["model_morph_key"]); // my change
            $table->index(
                [$columnNames["model_morph_key"], "model_type"],
                "model_has_roles_model_id_model_type_index"
            );

            $table
                ->foreign(PermissionRegistrar::$pivotRole)
                ->references("id") // role id
                ->on($tableNames["roles"])
                ->onDelete("cascade");
            if ($teams) {
                // $table->unsignedBigInteger($columnNames["team_foreign_key"]);
                $table->uuid($columnNames["team_foreign_key"]);
                $table->index(
                    $columnNames["team_foreign_key"],
                    "model_has_roles_team_foreign_key_index"
                );

                $table->primary(
                    [
                        $columnNames["team_foreign_key"],
                        PermissionRegistrar::$pivotRole,
                        $columnNames["model_morph_key"],
                        "model_type",
                    ],
                    "model_has_roles_role_model_type_primary"
                );
            } else {
                $table->primary(
                    [
                        PermissionRegistrar::$pivotRole,
                        $columnNames["model_morph_key"],
                        "model_type",
                    ],
                    "model_has_roles_role_model_type_primary"
                );
            }
        });

       Schema::create($tableNames["role_has_permissions"], function (
            Blueprint $table
        ) use ($tableNames) {
            // $table->unsignedBigInteger(PermissionRegistrar::$pivotPermission);
            // $table->unsignedBigInteger(PermissionRegistrar::$pivotRole);
            $table->uuid(PermissionRegistrar::$pivotPermission); // change uuid
            $table->uuid(PermissionRegistrar::$pivotRole); // change uuid

            $table
                ->foreign(PermissionRegistrar::$pivotPermission)
                ->references("id") // permission id
                ->on($tableNames["permissions"])
                ->onDelete("cascade");

            $table
                ->foreign(PermissionRegistrar::$pivotRole)
                ->references("id") // role id
                ->on($tableNames["roles"])
                ->onDelete("cascade");

            $table->primary(
                [
                    PermissionRegistrar::$pivotPermission,
                    PermissionRegistrar::$pivotRole,
                ],
                "role_has_permissions_permission_id_role_id_primary"
            );
        });

Is this a norm change to migration for UUID?

[parallels999]

From my POV 98 is a cutted UUID on role_id

laravel-permission/tests/Role.php

Lines 5 to 33 in a769221

	class Role extends \Spatie\Permission\Models\Role
	{
	protected $primaryKey = 'role_test_id';
	protected $visible = [
	'role_test_id',
	'name',
	];
	protected static function boot()
	{
	parent::boot();
	static::creating(function ($model) {
	if (empty($model->{$model->getKeyName()})) {
	$model->{$model->getKeyName()} = \Str::uuid()->toString();
	}
	});
	}
	public function getIncrementing()
	{
	return false;
	}
	public function getKeyType()
	{
	return 'string';
	}
	}

but the team feature to cause the error.

How?

Is this a norm change to migration for UUID?

I already post the norm change to migration for UUID

[parallels999]

Did you set your custom models on config?

laravel-permission/config/permission.php

Lines 16 to 27 in a769221

	'permission' => Spatie\Permission\Models\Permission::class,
	/*
	* When using the "HasRoles" trait from this package, we need to know which
	* Eloquent model should be used to retrieve your roles. Of course, it
	* is often just the "Role" model but you may use whatever you like.
	*
	* The model you want to use as a Role model needs to implement the
	* `Spatie\Permission\Contracts\Role` contract.
	*/
	'role' => Spatie\Permission\Models\Role::class,

[carveybunt]

Oh, this is my fault. I didn't set it. I set it right away and retrieve it immediately. Thank you very much.

[carveybunt]

It working now, and I sincerely thank you again.