Bug: Index from constrained() is not created on second FK when a composite unique index exists (Possible Regression of #49392)

[Caio-Tera]

Laravel Version

12.x

PHP Version

8.3.x

Database Driver & Version

PostgreSQL 17.x

Description

When creating a table with two foreign keys using ->constrained(), if a composite unique index is also defined on the first foreign key, the B-tree index for the second foreign key is not created.

This appears to be a regression or a new edge case related to the bug that was supposedly fixed in Pull Request #49392. The schema builder's optimization logic seems to be too aggressive, incorrectly omitting necessary indexes.

Steps To Reproduce

1. Create a fresh Laravel 12 project.
2. Create the following migration:

<?php

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

return new class extends Migration
{
    public function up(): void
    {
        // Parent table 1
        Schema::create('authors', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->timestamps();
        });

        // Parent table 2
        Schema::create('categories', function (Blueprint $table) {
            $table->id();
            $table->string('name')->unique();
            $table->timestamps();
        });

        // The problematic table
        Schema::create('books', function (Blueprint $table) {
            $table->id();

            // FK #1
            $table->foreignId('author_id')->constrained();

            // FK #2
            $table->foreignId('category_id')->constrained();

            $table->string('title');
            $table->timestamps();

            // Composite unique index
            $table->unique(['author_id', 'title']);
        });
    }
};

3. Run php artisan migrate.

4. Connect to the PostgreSQL database and run the following query to inspect the created indexes:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'books';

Expected Behavior

The query should return three indexes:

1. books_pkey (Primary Key)
2. books_author_id_title_unique (Composite Unique)
3. books_category_id_index (The index for the category_id foreign key)

Actual Behavior

The query only returns two indexes. The books_category_id_index is missing:

| indexname                  | indexdef                                                              |
| -------------------------- | --------------------------------------------------------------------- |
| `books_pkey`               | CREATE UNIQUE INDEX books_pkey ON public.books USING btree (id)       |
| `books_author_id_title_unique` | CREATE UNIQUE INDEX books_author_id_title_unique ON public.books USING btree (author_id, title) |

This confirms the index on the second foreign key was not created. Adding $table->index('category_id'); explicitly works as a temporary solution.

This issue was also independently reported in Discussion #54343.

[theparadoxShin]

Hi!

I'd like to work on fixing this issue. I've identified the problem. when using composite unique indexes, Laravel's index optimization logic incorrectly skips creating indexes for subsequent foreign keys.

I plan to:

1. Do your steps to reproduce the bug
2. Fix the index creation logic
3. Ensure backward compatibility as possible

Is it okay if I work on this ?

[Caio-Tera]

Go for it! I don't think I could solve this on my own. But I'll try to figure it out, and if I find anything, I'll post it here. Good luck, happy coding, and happy hunting.

[github-actions]

Thank you for reporting this issue!

As Laravel is an open source project, we rely on the community to help us diagnose and fix issues as it is not possible to research and fix every issue reported to us via GitHub.

If possible, please make a pull request fixing the issue you have described, along with corresponding tests. All pull requests are promptly reviewed by the Laravel team.

Thank you!

[theparadoxShin]

Hello everyone,

Just wanted to update you that the bug has been fixed.

While investigating, I discovered that the root cause was deeper than the missing composite unique index. As you pointed out @Caio-Tera , that was an issue, but the individual indexes for author_id and category_id were also failing to create.

The problem stemmed from the ->constrained() function. It seems that when a pre-existing index isn't detected, we must manually define one on the column before returning the foreign key definition. After that, the addFluentIndexes() Blueprint function works as expected.

PR #56312