Laravel 5 - Add column and foreign key to an existing table with data

Question

I am very new at Laravel! But I'm trying. Here is were I could use some help;

I have a posts table. I have a user table. But I forgot to add a foreign key in the posts table that links to the user id.

The create users table migration:

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

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

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

The create posts table migration:

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

class CreatePostsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->increments('id');
            $table->string('title');
            $table->text('body');
            $table->timestamps();
        });
    }

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

I have created the new migration file:

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

class AlterTablePostsAddForeignUser extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
      Schema::table('posts', function (Blueprint $table) {
          // I HAVE NO IDEA WHAT TO DO NEXT
      });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        //
    }
}

I have no idea how to fill up the "public function up()" method here! If anyone can help! It will be much appreciated!

Answer 1

I tried this and this worked for me.

Schema::table('posts', function (Blueprint $table) {
      $table->integer('user_id')->nullable()->unsigned();
      $table->foreign('user_id')->references('id')->on('users');
 });

Here when you are trying to add foreign key, by default it will try to have a Null value for new user_id column of the existing posts records.This 'null referencing' may cause the issue because the Database will expects a value for it by default. Using nullable() foreign key will avoid that issue because we explicitly says to Database that this upcoming Foreign Key field may consist a NULL value.

In detail explanation - Before you create the foreign key in Posts table, there might be some existing records in posts table. Whenever you are trying to create a foreign key,the existing records in the posts will try to have a NULL value in their upcoming Foreign Key column (user_id) which is called as null reference. Null referencing is a Database violation because by default Database expects a value for a foreign key field. So the Database will throw an error in this case.

To avoid the error we explicitly says Database that we will allow NULL values for this certain foreign key column user_id, using the nullable() function. So when ever the migration happen successfully the existing records will have a NULL value in its foreign key field and the values can be add later on (Or we can leave it as null if needed)

Answer 2

See the docs here

You need to install doctraine/dbal before modifying a column, be sure to add the doctrine/dbal dependency to your composer.json file. The Doctrine DBAL library is used to determine the current state of the column and create the SQL queries needed to make the specified adjustments to the column and the you should try what I found on this SO post

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

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

change() method for change structure of column

after this run the artisan command

php artisan migrate

if this doesn't work for you shoot the comment here! :)