Laravel 11 SQLite Migration Failure: after drop column: unknown column "XXXXXX" in foreign key definition

[Gandhi11]

Laravel Version

11.6.0

PHP Version

8.3.6

Database Driver & Version

SQLite 3.45.1

Description

In Laravel 11, running tests in memory using SQLite I receive this error when trying to drop a column that have a foreign key on it. That wasn't a problem before when using doctrine/dbal (#48864).

Steps To Reproduce

1. Create a new Laravel project.
2. Add a new migration that drop the user_id column on the sessions table like so:

Schema::table('sessions', function (Blueprint $table) {
    $table->dropColumn('user_id');
});

3. Setup PHPUnit for testing with the connection set to SQLite and the database set to :memory:.
4. Add use RefreshDatabase; in the default TestCase class
5. Run the test test_the_application_returns_a_successful_response.
6. You should receive the following error:

There was 1 error:

1) Tests\Feature\ExampleTest::test_the_application_returns_a_successful_response
Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1 error in index sessions_user_id_index after drop column: no such column: user_id (Connection: sqlite, SQL: alter table "sessions" drop column "user_id")

[hafezdivandari]

Unlike doctrine/dbal that creates a new table and drops the old one to drop a column on SQLite, Laravel schema uses alter table drop column SQL command. According to SQLite docs this command will fail if the column is indexed.

So you may drop its index before dropping the column:

Schema::table('sessions', function (Blueprint $table) {
    $table->dropIndex(['user_id']);
    $table->dropColumn('user_id');
});

[driesvints]

Thanks @hafezdivandari

[Gandhi11]

That doesn’t work for me…

The SQLite doc also say that the command will fail if The column is used in a foreign key constraint., so just dropping the index is not enough unfortunately.

[driesvints]

@Gandhi11 in that case you'll have to drop the foreign constraint as well beforehand.

[Gandhi11]

@driesvints And how I am supposed to do that? Dropping foreign keys is not supported by the SQLite driver.

[driesvints]

Sorry, wasn't aware of that.

@hafezdivandari what's your take here?

[hafezdivandari]

I haven't tested this, but you may try to temporary disable foreign key constraints and see if it works:

Schema::withoutForeignKeyConstraints(function () {
    Schema::table('sessions', function (Blueprint $table) {
        $table->dropIndex(['user_id']);
        $table->dropColumn('user_id');
    });
});

However, these limitions are related to SQLite, not Laravel. That's how drop column on SQLite works. It also doesn't support altering table and add/drop a foreign key.

[Gandhi11]

@hafezdivandari unfortunately this doesn't work, I still receive an error on the foreignKey when the migration tries to drop the column.

SQLSTATE[HY000]: General error: 1 error in table sessions after drop column: unknown column "user_id" in foreign key definition (Connection: sqlite, SQL: alter table "sessions" drop column "user_id")

The problem on the drop appear at the moment the foreignId is marked as constrained like so.

$table->foreignId('user_id')->nullable()->index()->constrained('users');

If there is no constrained, there is no problem. This wasn't a problem before when using doctrine/dbal since it was recreating the table without the constrained foreign.

I also tried with DB_FOREIGN_KEYS=false and still get the same error message, the foreign is added and block the drop of the column.

[hafezdivandari]

I'm sorry, again, that's how SQLite works! The only thing comes to mind right now is to implement dropForeign for SQLite; Wouldn't be easy because foreign key constraints don't have name on SQLite unlike other DBs, also we have to recreate the table that may causes unexpected results when using dropForeign in combination with other schema operations.

[Gandhi11]

Thank you for your response. And yes I understand that it is how SQLite works but that edge case was handle before and always worked since Laravel 5 when using doctrine/dbal.

I get that now since Laravel 11 it is using alter table to drop columns with SQLite 3.45 but it seems that it is causing something that was working to break.

Do you know if it would be possible use doctrine/dbal for that edge case?

[ocleroux]

I've been experiencing the same issue recently, so I'm bumping this issue, hoping for a solution!

[hafezdivandari]

I'm preparing a PR, mostly done, hopefully we can fix this.

[hafezdivandari]

You may check PR #51373, hopefully if it's merged you can do:

Schema::table('sessions', function (Blueprint $table) {
    $table->dropIndex(['user_id']);
    $table->dropForeign(['user_id']);
    $table->dropColumn('user_id');
});

// Or...
Schema::table('sessions', function (Blueprint $table) {
    $table->dropIndex(['user_id']);
    $table->dropConstrainedForeignId('user_id');
});