Create a UNIQUE KEY constraint on roles table ('name', 'guard_name')

[lukewatts]

Problem

Recently on a project which uses Laravel Permission, we noticed we had an issue where one of our apps was getting duplicate roles in the 'web' guard. So we had something like this as a result in our roles table:

id	name	guard_name	created_at	updated_at
1	admin	web	2023-01-10 12:11:10	2023-01-10 12:11:10
2	editor	web	2023-01-10 12:11:11	2023-01-10 12:11:11
3	editor	web	2023-01-10 12:11:11	2023-01-10 12:11:11
4	moderator	web	2023-01-10 12:11:12	2023-01-10 12:11:12
5	moderator	web	2023-01-10 12:11:12	2023-01-10 12:11:12
6	moderator	web	2023-01-10 12:11:12	2023-01-10 12:11:12

Solution

Add a UNIQUE Key constraint on ('name', 'guard_name') on the roles table so each guard cannot have more than one occurrence of a name.

I can't see a reason you'd ever want duplicates within a guard_name.

[erikn69]

it has unique constraint on ('name', 'guard_name'), you break something

laravel-permission/database/migrations/create_permission_tables.php.stub

Line 49 in 625a69d

$table->unique(['name', 'guard_name']);

[drbyte]

it has unique constraint on ('name', 'guard_name')

Correct. This has always been enforced by code, but also with db constraint since v4.0.0

[lukewatts]

Ah, ok. I didn't realise it was added in v4.

Is there a command that needs to be run to add the indexes for an existing table?

We can't re-create these tables from scratch at this point.

I'm guessing what happened is that we had a v3.x version when we first migrated the tables and since then we've upgraded to v4.4 but still don't have the unique indexes on permissions or roles

Thanks

[drbyte]

A couple steps are needed. Probably best to do them manually.

1. First you must get rid of the duplicates. But before deleting any you'll want to ensure the duplicate roles are not currently in use by any users, and unassign any permissions from them.

Probably simplest to do that via Tinker.

Do a database backup first, for safeguard.

Then use something like:

$role = Role::findById(3); // because 3 is the duplicate of 2 from your chart above
$users = $role->users->count();

If the count is 0, then it's safe to $role->delete();, because that should also delete the permissions assigned to it.
If the count is not 0, then reverse it and check for role id 2. If it's 0 instead, then delete role 2.
If both are not count of 0, then you'll need to pick a role number to keep, and assign all the other roles' users to that kept role. And then you can delete the extra role.

Do you follow?

Once that's done ...

2. You'll need to apply the constraint to the table.
   Simplest is probably to diff-compare the migration file you've got in your repo with the one in the version of the package that you're using, and prepare a new migration file to apply the missing constraints.