Adding Unique Field to MySQL Table With Existing Records

Another quick tip, this time around adding a uniqued field to a MySQL table that already has data in it using a Laravel migration.

Take the below example:

 1class AddSlugToPostsTable extends Migration
 2{
 3    public function up()
 4    {
 5        Schema::table('posts', function (Blueprint $table) {
 6            $table->string('slug')->unique()->after('title');
 7        });
 8    }
 9 
10    public function down()
11    {
12        Schema::table('posts', function (Blueprint $table) {
13            $table->dropColumn('slug');
14        });
15    }
16}

A real simple migration to add a slug field to an existing posts table. If you run this migration with existing data in the posts table, you’ll come across this error:

1SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '' for key 'posts_slug_unique'

This is because you’re not giving each row in the table a unique value to populate slug with, so it’s failing on the 2nd row (as it has the same empty string value as the first row). You can’t set a default value for this field either, as you’ll end up with the same error.

The workaround to this is relatively simple, but may not be immediately obvious: perform the operation in steps. Create the slug field without the unique constraint, populate it with unique values, and finally add the unique constraint.

 1public function up()
 2{
 3    // Step 1: add the slug field without unique()
 4    Schema::table('posts', function (Blueprint $table) {
 5        $table->string('slug')->after('title');
 6    });
 7 
 8    // Step 2: Update each row to populate the slug field
 9    DB::table('posts')->get()->each(function ($post) {
10        DB::table('posts')->where('id', $post->id)->update(['slug' => str_slug($post->title)]);
11    });
12 
13    // Step 3: add the unique constraint to slugs
14    Schema::table('posts', function (Blueprint $table) {
15        $table->unique('slug');
16    });
17}

Migrations can be extremely useful for migrating your data as well as your schema. Don’t be shy to use the DB facade inside them, but try to stay clear of using Eloquent models directly. Your migrations should be immutable, so that once they’re run in production they never change. Eloquent models, by nature, change over time along with your codebase and adapt their behavior to suit new business requirements, which can lead to your old migrations subtly changing their behavior as well.