Not able to add unique to products table through Laravel migration

0
votes

enter image description here

I am trying to add unique to slug in products table and I have the following migration. However when I run php artisan migrate I get an error.

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class AddUniqueToProductsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('products', function (Blueprint $table) {
           $table->unique('slug'); 
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('products', function (Blueprint $table) {
            $table->dropUnique('products_slug_unique');
        });
    }
}

Error

➜ php artisan migrate

[Illuminate\Database\QueryException] SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '' for key 'products_slug_unique' (SQL: alter table products add unique products_slug_unique(slug))

[Doctrine\DBAL\Driver\PDOException] SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '' for key 'products_slug_unique'

[PDOException] SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '' for key 'products_slug_unique'

What am I doing wrong here?

3
laravellaravel-5.3
Make sure your table is empty before running the migration. - Amit Gupta
Well I want to add unique to an existing field. Can I do that? Or I need to drop slug column? - shin
Then make sure existing data in slug column is unique. - Amit Gupta
I tried to drop a column, but it didn't work. public function up() { Schema::table('products', function (Blueprint $table) { $table->dropColumn('slug'); $table->string('slug')->unique(); }); } Error: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'slug' (SQL: alter table products add slug varchar(255) not null) ... - shin
Don't write migration for this, just make it null by running the query as update table_name set slug = NULL;. Then run your unique migration. - Amit Gupta

3 Answers

1
votes

You can create normal field of 'slug' and create data type of slug is 'string' and you can use validator to create unique slugs, in this case you can give custom response also.

 $validator = Validator::make($request->all(), [
        'slug' => 'required|string|unique:products'
    ]);
 if ($validator->fails()){
  //custom response
 }
0
votes

Set the default for unique column to NULL before putting constraint. Since the values in slug is net to empty but not null MySql is expecting '' to be unique itself, thus preventing unique constraint on column. 

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    DB::statement('UPDATE products SET slug = NULL;');

    Schema::table('products', function (Blueprint $table) {
       $table->unique('slug'); 
    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::table('products', function (Blueprint $table) {
        $table->dropUnique('products_slug_unique');
    });

    DB::statement("UPDATE products SET slug = '';");
}
0
votes

As outlined in the Migrations guide to fix this all you have to do is edit your AppServiceProvider.php file and inside the boot method set a default string length:

 use Illuminate\Database\Schema\Builder;


public function boot()
{
    Builder::defaultStringLength(191);
}