Voyager: SQLSTATE[HY000]: General error: 1832 Cannot change column 'role_id': used in a foreign key constraint 'users_role_id_foreign' (SQL: ALTER TABLE users CHANGE role_id role_id INT DEFAULT NULL

[asakaze]

• Laravel Version: 5.6.17
• Voyager Version: 1.1.0
• PHP Version:
• Database Driver & Version:

Description:

Rollback for migration 2017_11_26_013050_add_user_role_relationship.php doesn't work. Out of the box I get error:

IlluminateDatabaseQueryException : SQLSTATE[HY000]: General error: 1832 Cannot change column 'role_id': used in a foreign key constraint 'users_role_id_foreign' (SQL: ALTER TABLE users CHANGE role_id role_id INT DEFAULT NULL)

at /vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
660| // If an exception occurs when attempting to run a query, we'll format the error
661| // message to include the bindings with SQL, which will make this exception a
662| // lot more helpful to the developer instead of just the database's errors.
663| catch (Exception $e) {

664| throw new QueryException(
665| $query, $this->prepareBindings($bindings), $e
666| );
667| }
668|

Steps To Reproduce:

Use php artisan migrate:rollback.

[agoalofalife]

This is a bug

[agoalofalife]

You must remove the following line
https://github.com/the-control-group/voyager/blob/1.1/migrations/2017_11_26_013050_add_user_role_relationship.php#L31

[agoalofalife]

@asakaze push PR https://github.com/the-control-group/voyager/pull/3073

[fletch3555]

3073 merged, closing

[fletch3555]

@andreebit, This has already been fixed in #3073, which included a breaking change, so it was merged into 1.x in preparation for a future 1.2 release

[andreebit]

I don't think this is the best way to solve this issue, but I found a workaround. The only thing you have to do is to copy this migration to your project migrations folder.

Hope it will help you.

This is the solution:

public function down()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropForeign(['role_id']);
        });

        Schema::table('users', function (Blueprint $table) {
            \DB::statement("ALTER TABLE `{$table->getTable()}` CHANGE COLUMN `role_id` `role_id` INT(11) NOT NULL");
        });

        Schema::table('permission_role', function (Blueprint $table) {
            $table->dropForeign(['role_id']);
        });

        Schema::table('permission_role', function (Blueprint $table) {
            \DB::statement("ALTER TABLE `{$table->getTable()}` CHANGE COLUMN `role_id` `role_id` INT(11) NOT NULL");
        });

        Schema::table('roles', function (Blueprint $table) {
            \DB::statement("ALTER TABLE `{$table->getTable()}` CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT FIRST");
        });

        Schema::table('users', function (Blueprint $table) {
            $table->foreign('role_id')->references('id')->on('roles');
        });

        Schema::table('permission_role', function (Blueprint $table) {
            $table->foreign('role_id')->references('id')->on('roles');
        });
    }

Uploading 2017_11_26_013051_add_user_role_relationship_fix.php.zip…