Bug: Database Migrations - Problem in resolving constrained table name

[raghed-kahil]

Laravel Version

11.19.0

PHP Version

8.3.4

Database Driver & Version

SQLITE

Description

when I was trying to insert into categories table i get this error

   INFO  Seeding database.


   Illuminate\Database\QueryException 

  SQLSTATE[HY000]: General error: 1 no such table: main.parent_categories (Connection: sqlite, SQL: insert into "categories" ("name", "slug") values (tech, tech))

the solution was to specify manually constrained table name like this

$t->foreignIdFor(Category::class, 'parent_category_id')->nullable()->constrained('categories')->onDelete('cascade');

categories migration

<?php

use App\Models\Category;
use App\Models\Media;
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::create('categories', function (Blueprint $t) {
            $t->id();
            $t->string('name');
            // before fix
            $t->foreignIdFor(Category::class, 'parent_category_id')->nullable()->onDelete('cascade');
            // afted fix
            $t->foreignIdFor(Category::class, 'parent_category_id')->nullable()->constrained('categories')->onDelete('cascade');
            $t->string('slug');
            $t->foreignIdFor(Media::class)->nullable();
            $t->string('description')->nullable();
            $t->string('status')->default('published');
            $t->timestamps();

            $t->unique(['parent_category_id', 'slug']);
        });
    }

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

Steps To Reproduce

Create this migration and try to insert into categories

<?php

use App\Models\Category;
use App\Models\Media;
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::create('categories', function (Blueprint $t) {
            $t->id();
            $t->string('name');
            $t->foreignIdFor(Category::class, 'parent_category_id')->nullable()->onDelete('cascade');
            $t->string('slug');
            $t->foreignIdFor(Media::class)->nullable();
            $t->string('description')->nullable();
            $t->string('status')->default('published');
            $t->timestamps();

            $t->unique(['parent_category_id', 'slug']);
        });
    }

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

[rodrigopedra]

What is the bug?

The schema builder does not keep a copy of the model instance. So when calling constrained() it tries to guess the table from the foreign key name.

As you are using a custom foreign key name (due to a self-referencing foreign key), it correctly tries to guess from that name.

From the docs:

... while the constrained method will use conventions to determine the table and column being referenced. If your table name does not match Laravel's conventions, you may manually provide it to the constrained method.

Reference: https://laravel.com/docs/11.x/migrations#foreign-key-constraints

When using a custom foreign key, just provide the table name to the constrained() method.

Also, why not just using category_id as the foreign key name?

[raghed-kahil]

you were right i assumed that it will guess table name based on the model class.
but it will be good feature to add.

[raghed-kahil]

the reason i didn't noticed the wrong table name is because sqlite didn't check for constrained table existence when creating foreign key.

[rodrigopedra]

No problem =)

Just saw an open PR which seams to improve the expectations around this: #53144