Handling foreign checks in Laravel 5 migrations

Question

I have a calls table with user_id column and a foreign key constrain. So, user_id is related to users table.

I made a migration which deletes the column and foreign key constrain. The up method works fine. However, when it comes to down it breaks down because I add a user_id column and then restore the constrain, but the values in the column have nothing to do with real data so I got 150 MySQL error.

I've found a solution which didn't work for me. The solution is to temporarily disable foreign key constrains. Here is my migration file:

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

class RemoveUseridFromCalls extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('calls', function(Blueprint $table)
        {
            $table->dropForeign('calls_user_id_foreign');
            $table->dropColumn('user_id');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        \DB::statement('SET FOREIGN_KEY_CHECKS = 0');

        Schema::table('calls', function(Blueprint $table)
        {
            $table->integer('user_id')->unsigned();

            $table->foreign('user_id')
                ->references('id')
                ->on('users')
                ->onDelete('set null');
        });

        \DB::statement('SET FOREIGN_KEY_CHECKS = 1');
    }

}

But I still get the same error.

So, the questions are:

1. What would you do if you need to remove user_id from columns table as it's not needed anymore, however you have to write the down method as well as up?

2. Why disabling foreign key checks doesn't work?

I use InnoDB as table type for all my tables.

Answer 1

You can simply use onDelete('cascade').

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::create('calls', function(Blueprint $table)
    {
        $table->increments('id');
        $table->integer('user_id')->unsigned();
        $table->timestamps();

        $table->foreign('user_id')
            ->references('id')
            ->on('users')
            ->onDelete('cascade'); // use 'cascade'
    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::drop('calls');
}

}

onDelete('cascade') tells Laravel to delete a calls row when it's corresponsding foreign key user_id is deleted.

Answer 2

I had the same problem and fixed it by separating migration into several files. First one creates the table, the others add columns for the keys and the keys themselves.

After that everything starts working like a charm.

Answer 3

To check foreign key in table while dealing with migration can be as follow:

$keyExists = DB::select(DB::raw('SHOW KEYS FROM TABLE_NAME WHERE Key_name=\'YOUR_FOREIGN_KEY_NAME\''));

if(!$keyExists){
    $table->foreign('app_id')
        ->references('id')
        ->on('apps')
         ->onDelete('cascade');
}