Altering an SQLite table with a foreign key constraint fails

[simonworkhouse]

• Laravel Version: 9.23.0
• PHP Version: 8.1.8
• Database Driver & Version: SQLite 3.31.1

Description:

This issue appears to only occur when using doctrine/dbal:^3.4.

Invalid SQL is generated when altering a column if the table has a foreign key constraint.

SQLSTATE[HY000]: General error: 1 near "0": syntax error (SQL: CREATE TABLE orders (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, user_id INTEGER DEFAULT NULL, reference VARCHAR(255) DEFAULT NULL COLLATE BINARY, CONSTRAINT 0 FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE))

Steps To Reproduce:

Create a migration with the following contents and attempt to execute it using the SQLite driver:

Schema::create('orders', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('reference')->unique();
    $table->unsignedBigInteger('user_id')->nullable();
    $table->foreign('user_id')
            ->references('id')->on('users')
            ->onDelete('cascade');
});

Schema::table('orders', function (Blueprint $table) {
    $table->string('reference')->nullable()->change();
});

See https://github.com/workhouse-advertising/laravel-sqlite-bug for an example.

[driesvints]

Please report this on the Doctrine repo that generates the SQL.

[simonworkhouse]

@driesvints Is this a known issue or are you just certain that fault lies with that package? I was reporting it here because it's a required dependency for laravel/framework when altering columns.

[driesvints]

We don't generate the SQL. So if the SQL is incorrectly generated it's happening in doctrine/dbal and not in Laravel.

[simonworkhouse]

@driesvints That would be assuming that methods haven't been removed and that they haven't had their definitions changed, etc... but sure, if you're certain that the fault lies with that package then why are you asking me to report the bug for you? This isn't exactly an uncommon use-case, this issue exists with any new Laravel install following your documentation.

[freshleafmedia]

Associated issue: doctrine/dbal#5572

[simonworkhouse]

It appears that this was an issue previously #34093