Foreign key constraint fails when adding a foreign key to an existing table in Laravel

Question

I have a table order_lines, which has an order_id that belongs to orders. Now I forgot to add the foreign key to the initial migration, now the website is up and running and there's life data in the database.

The foreign key I would like to add would delete an order line when an order is deleted.

I created the following migration add_order_lines_order_id_foreign_to_order_lines_table

class AddOrderLinesOrderIdForeignToOrderLinesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('order_lines', function (Blueprint $table) {
            $table->foreign('order_id', 'order_lines_order_id_foreign')->references('id')->on('orders')
                ->onDelete('cascade');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('order_lines', function (Blueprint $table) {
            $table->dropForeign('order_lines_order_id_foreign');
        });
    }
}

The only things this is doing is it's (trying to) add a foreign key constraint, so that when future orders get deleted, the order_lines get deleted with it.

This is the error I'm receiving when I try to run the migration

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (bud001_miguel.#sql-5e1_109923, CONSTRAINT order_lines_order_id_foreign FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE) (SQL: alter table order_lines add constraint order_lines_order_id_foreign foreign key (order_id) references orders (id) on delete cascade)

This is the original orders table definition

class CreateOrdersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('orders', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('user_id')->nullable();
            $table->unsignedBigInteger('shipping_address_id')->nullable();
            $table->unsignedBigInteger('billing_address_id')->nullable();
            $table->text('shipping_address_data')->nullable();
            $table->text('billing_address_data')->nullable();
            $table->timestamps();
        });

        Schema::table('orders', function (Blueprint $table) {
            $table->foreign('user_id')->references('id')->on('users')->onDelete('set null');
            $table->foreign('shipping_address_id')->references('id')->on('addresses')->onDelete('set null');
            $table->foreign('billing_address_id')->references('id')->on('addresses')->onDelete('set null');
        });
    }

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

This is the original order_lines table definition

class CreateOrderLinesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('order_lines', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('order_id');
            $table->unsignedBigInteger('animal_id');
            $table->unsignedBigInteger('product_plan_id');
            $table->unsignedInteger('price');
            $table->unsignedInteger('daily_price');
            $table->text('animal_data')->nullable();
            $table->text('plan_data')->nullable();
            $table->timestamps();
        });
    }

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

What I've tried

• Disable foreign key checks by adding the following statement to the migration DB::statement('SET FOREIGN_KEY_CHECKS=0;');

But the error persists.

Answer 1

Your problem is there are some records in the order_lines table with order_ids who do not exist in the orders table.

The migration is trying to enforce the foreign key but can not. Run a query to find those rows and deal with them, then run your migrations again