laravel 5 - How can i add ->index() to existing foreign key

Question

Framework: Laravel 5.2 Database: MySQL PHP: 7.0

I have the table "pops":

    Schema::create('pops', function (Blueprint $table) {
        $table->string('id')->primary()->index();
        $table->string('ab_test_parent_id')->nullable();
        $table->string('cloned_parent_id')->nullable();
        $table->timestamps();
    });

And the table "conversions":

    Schema::create('conversions', function (Blueprint $table) {
        $table->string('id')->primary()->index();
        $table->integer('users')->default(null);
        $table->string('name',256)->default(null);
        $table->string('pop_id');
        $table->foreign('pop_id')->references('id')->on('pops')->onDelete('cascade')->onUpdate('cascade');
        $table->timestamps();
    });

I have set a foreign key (pop_id) in the "conversions" table. Now does it's mean that the foreign key (pop_id) is also a index? if not... what i need to do in order to make it a index?

Thank?

Answer 1

You can just do this.

<?php

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

class AddIndexToLeads extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('leads', function(Blueprint $table)
        {
            $table->index('trader_id');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('leads', function (Blueprint $table)
        {
            $table->dropIndex(['trader_id']);
        });
    }

}

credit to @bobbybouwmann

Answer 2

I was facing problem when i just write :-

$table->index('column_name');

So to be more exact first you need to create column with desired column type then assign it to be index like this :-

  $table->string('column_name');
  $table->index('column_name');
  //or
  $table->string('column_name')->index();

I hope this helps

Answer 3

Here is a perfect solution for you.

Up

$table->string('poptin_id')->index('poptin_id');

Down

$table->dropIndex(['poptin_id']);

Suggestion to fix your migrations

$table->string('id')->primary()->index();

replace above with below

$table->increments('id');

and fix your foreign key as below

$table->integer('poptin_id')->index('poptin_id')->unsigned();

Answer 4

Laravel only adds a foreign key constraint and doesn't add index implicitly. But some databases such as MySQL automatically index foreign key columns.

If you need to add index to a field in another migration. Then you could do

$table->index('email');