How to correctly remove 'unique' column attribute in users table column in Laravel 5.3?

1
votes

Following tutorials early on left me with a 'username' column that is set to unique. I would like to change that, so that my unique user identifier is tied to the email.

I've done the steps I found:

  • composer require doctrine/dbal
  • php artisan make:migration modify_users_table --table=users

My migration contains:

public function up()
{
    Schema::table('users', function (Blueprint $table) {
        $table->string('username')->unique(false)->change();
    });
}

However, when I run the command 'php artisan migrate' I get

[Illuminate\Database\QueryException]
SQLSTATE[42000]: Syntax error or access violation: 1280 Incorrect index name '' (SQL: alter table users add unique (us
ername))

[Doctrine\DBAL\Driver\PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1280 Incorrect index name ''

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1280 Incorrect index name ''

From my initial research it appears that the issue is with my index:

users_username_unique

So my question is:

Is it safe to remove that index, then run the migration?
If that is the correct way to do it, does my migration need to have something like this:

$table->dropUnique('users_username_unique');

Then will this command automatically create a correct, non-unique, index?

$table->string('username')->unique(false)->change();
3
phpmysqllaravellaravel-5.3
Why not to fix it via MySQL? If you gonna use this migration again, you cant remove unique() after that. – zhekaus
I had thought about that, because you're right, I won't be able to use the migrations again without some editing. But I'm pretty sure I can remove the ->unique() from the initial user table migration, then remove these alterations - then I might be able to use the migrations again in the future. – ffffff

3 Answers

1
votes

Drop a unique index and add a plain index.

$table->dropUnique(['username']);
$table->index('username');

If need, add a unique index for email.

$table->unique('email');

Database: Migrations

2
votes

For dropUnique() method, You have to create a new migration,

 Schema::table('users', function (Blueprint $table) {
         $table->dropUnique('username');  
 });

or If need, you can add a unique index for username.

Schema::table('users', function (Blueprint $table) {
        $table->unique('username');  
 });
0
votes

You need to create a new migration and use dropUnique() method:

Schema::table('users', function (Blueprint $table) {
    $table->dropUnique('users_username_unique');
});

Then run composer du and php artisan migrate commands to execute the migration.