Adding Nullable to existing Foreign Keys

Hi I am tring to add nullable to the receipt_id & dispatch_id column of a table and receive the folowing exception. What wold be the correct way of doing this??

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'db_vanilla.make_dispatch_and_receipt_nullable' doesn't exist (Connection: mysql, SQL: alter table make_dispatch_and_receipt_nullable modify dispatch_id bigint unsigned null)

Existing table

<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::disableForeignKeyConstraints();

        Schema::create('comments', function (Blueprint $table) {
            $table->id();
            $table->foreignId('receipt_id')->constrained();
            $table->foreignId('dispatch_id')->constrained();
            $table->text('comment');
            $table->timestamps();
        });

        Schema::enableForeignKeyConstraints();
    }

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

2024_08_07_101357_update_comments_table_to_make_dispatch_and_receipt_nullable

<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::disableForeignKeyConstraints();

        Schema::table('comments', function (Blueprint $table) {
            // Drop foreign key constraints
            $table->dropForeign(['dispatch_id']);
            $table->dropForeign(['receipt_id']);
        });

        Schema::table('comments', function (Blueprint $table) {
            // Modify the columns to be nullable
            $table->unsignedBigInteger('dispatch_id')->nullable()->change();
            $table->unsignedBigInteger('receipt_id')->nullable()->change();
        });

        Schema::table('comments', function (Blueprint $table) {
            // Re-add foreign key constraints
            $table->foreign('dispatch_id')->references('id')->on('dispatches')->onDelete('cascade');
            $table->foreign('receipt_id')->references('id')->on('receipts')->onDelete('cascade');
        });

        Schema::enableForeignKeyConstraints();
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::disableForeignKeyConstraints();

        Schema::table('comments', function (Blueprint $table) {
            // Drop foreign key constraints
            $table->dropForeign(['dispatch_id']);
            $table->dropForeign(['receipt_id']);
        });

        Schema::table('comments', function (Blueprint $table) {
            // Modify the columns to be not nullable
            $table->unsignedBigInteger('dispatch_id')->nullable(false)->change();
            $table->unsignedBigInteger('receipt_id')->nullable(false)->change();
        });

        Schema::table('comments', function (Blueprint $table) {
            // Re-add foreign key constraints
            $table->foreign('dispatch_id')->references('id')->on('dispatches')->onDelete('cascade');
            $table->foreign('receipt_id')->references('id')->on('receipts')->onDelete('cascade');
        });

        Schema::enableForeignKeyConstraints();
    }
};

LaryAI

1 year ago

Level 58

To address the issue of making the dispatch_id and receipt_id columns nullable in your comments table, you need to ensure that you are modifying the correct table and handling the foreign key constraints properly. The error you encountered suggests that the table make_dispatch_and_receipt_nullable does not exist, which seems to be a mistake in your migration script.

Here is a corrected version of your migration script to make the dispatch_id and receipt_id columns nullable:

<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::disableForeignKeyConstraints();

        Schema::table('comments', function (Blueprint $table) {
            // Drop foreign key constraints
            $table->dropForeign(['dispatch_id']);
            $table->dropForeign(['receipt_id']);
        });

        Schema::table('comments', function (Blueprint $table) {
            // Modify the columns to be nullable
            $table->unsignedBigInteger('dispatch_id')->nullable()->change();
            $table->unsignedBigInteger('receipt_id')->nullable()->change();
        });

        Schema::table('comments', function (Blueprint $table) {
            // Re-add foreign key constraints
            $table->foreign('dispatch_id')->references('id')->on('dispatches')->onDelete('cascade');
            $table->foreign('receipt_id')->references('id')->on('receipts')->onDelete('cascade');
        });

        Schema::enableForeignKeyConstraints();
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::disableForeignKeyConstraints();

        Schema::table('comments', function (Blueprint $table) {
            // Drop foreign key constraints
            $table->dropForeign(['dispatch_id']);
            $table->dropForeign(['receipt_id']);
        });

        Schema::table('comments', function (Blueprint $table) {
            // Modify the columns to be not nullable
            $table->unsignedBigInteger('dispatch_id')->nullable(false)->change();
            $table->unsignedBigInteger('receipt_id')->nullable(false)->change();
        });

        Schema::table('comments', function (Blueprint $table) {
            // Re-add foreign key constraints
            $table->foreign('dispatch_id')->references('id')->on('dispatches')->onDelete('cascade');
            $table->foreign('receipt_id')->references('id')->on('receipts')->onDelete('cascade');
        });

        Schema::enableForeignKeyConstraints();
    }
};

Explanation:

Disable Foreign Key Constraints: This is done to avoid issues when modifying columns that are part of foreign key constraints.
Drop Foreign Key Constraints: The foreign key constraints on dispatch_id and receipt_id are dropped to allow modification of these columns.
Modify Columns to be Nullable: The dispatch_id and receipt_id columns are modified to be nullable.
Re-add Foreign Key Constraints: The foreign key constraints are re-added to the dispatch_id and receipt_id columns.
Enable Foreign Key Constraints: Foreign key constraints are re-enabled after the modifications.

This migration script should correctly make the dispatch_id and receipt_id columns nullable in your comments table.

Tray2

1 year ago

Level 74

I'm generally against nullable foreign keys, and suggest that you think the other way around.

I mean if you have a user => user_profile relation, you might want to add the user_profile_id to the users table. If a user then isn't required to have a profile, you would need to make it nullable, this is in my mind seldom a good practice, I much rather add a user_id column to the user_profiles table, that way I don't need to make anything nullable, because if the user has a profile, the user_id exists in the user_profiles table.

auroralabs

1 year ago

Level 6

The issue I have is that I have a Receipt table that allows a user to add multiple notes to the Comment table. I also have a Dispatch table that also allows a user to add multiple notes to the Comment table. However when a note is added via the Receipt form the exception is thrown because no dispatch_id is being added as is correct as it is adding a receipt_id to link the note to a receipt record.

Just now Dispatch & Receipt hasMany relationship to Comment and Comment belongsTo Dispatch & Receipt

Tray2

1 year ago

Best Answer

Level 74

@auroralabs Then you need to fetch it before you create the record, or as I wrote make it the other way around.

Please or to participate in this conversation.