Adding column containing foreign key to existing table in production in Laravel

Question

I have a table participant which has a many-to-one relationship with table campaign. Now for some stupid reason I forgot to add a column campaign_id, which holds the foreign key to the row in table campaign. Now to fix this I could easily add the following line to the create_participants_table migration file and run the php artisan migrate:fresh command, which will drop all tables and recreate them with the right columns.

$table->unsignedBigInteger('campaign_id');

But the problem is that both tables are already in production and already contain data, so running migrate:fresh would not be optimal. So in that case I would create another migration file called add_campaign_id_to_participants like so:

public function up()
{
    Schema::table('participants', function (Blueprint $table) {
        $table->unsignedBigInteger('campaign_id');
    });
}

The problem is that when running php artisan migrate I get an error that it Cannot add a NOT NULL column with default value NULL. Which seems fair since the column isn't nullable and doesn't have a default value. But making the column nullable or set a default value doesn't seem desirable.

Now my participants table has a one-to-one relationship with table visitors, where every participant has a visitor, but not every visitor has a participant. Also the visitors table has a many-to-one relationship with table campaign. That means that in theory I could fill the just created campaign_id for a participant with something like:

$participant->visitor->campaign->id

Now my question is if this is somehow possible and if yes, how would I achieve this?

Answer 1

I fixed it by adding the following to the add_campaign_id_to_participants migration file.

public function up()
{
    Schema::table('participants', function (Blueprint $table) {
        $table->unsignedBigInteger('campaign_id')->default(0);
    });
    $participants = Participant::all();
    foreach($participants as $participant)
    {
        $participant->campaign_id = $participant->visitor->campaign_id;
        $participant->save();
    }
}