DEV Community

Ahmad Jamaly Rabib
Ahmad Jamaly Rabib

Posted on

Laravel: Alter a foreign key constraint

I did a mistake while creating the laravel migration file.

Added ON DELETE cascade in the foreign key and made the column not nullable. It was already deployed to live where it is merged with other commits from my fellow devs.

Now the users can't save any post without selecting the category which is very annoying...

So now I need to Alter the foreign key constraint.
Let's make the SQL query first.

We need to remove the current foreign key first.

So, to remove it we first need to get the name of the foreign key. To make the foreign key we need {table_name}_{column_name}_foreign.
Lets see from my migration

Schema::create('posts', function (Blueprint $table) {
    $table->increments('id');`
    $table->integer('category_id')->unsigned();`
    $table->foreign('category_id')->references('id')->on('categories')->onDelete('cascade');
});
Enter fullscreen mode Exit fullscreen mode

From here we found that our foreign key name will be posts_category_id_foreign.

Now we can remove the foreign key and column. This will be the query to drop the foreign key:

ALTER TABLE posts DROP FOREIGN KEY posts_category_id_foreign;
ALTER TABLE posts DROP COLUMN category_id;
Enter fullscreen mode Exit fullscreen mode

Then we can create the new nullable column and key:

ALTER TABLE posts ADD COLUMN category_id NULL;
ALTER TABLE posts ADD FOREIGN KEY (key) REFERENCES categories(id) ON DELETE SET NULL;
Enter fullscreen mode Exit fullscreen mode

Now let's see how we convert this in migration:

  • First create the migration file
php artisan make:migration update_posts_category_foreign
Enter fullscreen mode Exit fullscreen mode
  • In the up method lets first remove the current foreign key and column.
Schema::table('posts', function (Blueprint $table) {
  $table->dropForeign('posts_category_id_foreign');
  $table->dropColumn('category_id');
});
Enter fullscreen mode Exit fullscreen mode
  • Now as the column is removed we can now add the nullable column and new foreign key
Schema::table('posts', function (Blueprint $table) {
    $table->integer('category_id')->unsigned()->nullable();
    $table->foreign('category_id')->references('id')->on('posts')->onDelete('set null');
});
Enter fullscreen mode Exit fullscreen mode

That's it! In the down method we will just reverse it.

Schema::table('posts', function (Blueprint $table) {
    $table->dropForeign('posts_category_id_foreign');
    $table->foreign('category_id')->references('id')->on('posts')->onDelete('cascade');
});
Enter fullscreen mode Exit fullscreen mode

That's how I got rid from my mistake.
See you again! Happy coding!

Top comments (0)