Automatic/Implicit Index Creation for Foreign Keys (PostgreSQL and SQLite))

[mathiasgrimm]

Background

In MySQL, foreign key columns automatically receive corresponding indexes. However, PostgreSQL requires explicit index creation for foreign key columns. While this behavior is inherent to PostgreSQL and not related to Laravel, we can enhance Laravel's handling of this database-specific difference.

Despite our Schema DDL abstraction layer, migrations can produce different database structures when run on MySQL versus PostgreSQL/SQLite due to how each database handles index creation for foreign keys.

Proposed Solution

Add a configuration option in Laravel to automatically create indexes for PostgreSQL foreign key columns, making the behavior consistent with other supported databases.

Configuration

// config/database.php
'connections' => [
    'sqlite' => [
        // ... existing configuration ...
        'foreign_key_implicit_index_creation' => true,
    ],

    'pgsql' => [
        // ... existing configuration ...
        'foreign_key_implicit_index_creation' => true,
    ],
]

Current Usage

// Currently requires explicit index creation
$table->foreignId('tenant_id')->index()->constrained();

Desired Usage

// With the new configuration enabled
$table->foreignId('tenant_id')->constrained();

Implementation Considerations

A mechanism should be provided to override the automatic index creation when needed

References

MySQL

https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html

"MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created automatically."

SQLite

https://www.sqlite.org/foreignkeys.html

"an index should be created on the child key columns of each foreign key constraint"

PostgreSQL

https://www.postgresql.org/docs/current/indexes-unique.html

"PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint"

https://www.postgresql.org/docs/current/ddl-constraints.html

"Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns too. Because this is not always needed, and there are many choices available on how to index, the declaration of a foreign key constraint does not automatically create an index on the referencing columns."