Dropping sqlite column fails when updating laravel/eloquent model using database manager migration script

Question

I'm having an issue with attempting to run tests in my Laravel application. I have a table structure as follows:

These SQL Queries have been generated by an export script using TablePlus, as I figured it was the easiest way to share the table structure.

After Migration (NOW)

CREATE TABLE `business_system_role_location_type` (
  `business_system_role_id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `location_type_id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`business_system_role_id`,`location_type_id`),
  KEY `business_system_role_loc_type_fk` (`location_type_id`),
  CONSTRAINT `business_system_role_loc_type_fk` FOREIGN KEY (`location_type_id`) REFERENCES `location_types` (`id`),
  CONSTRAINT `business_system_role_loc_type_role_id_fk` FOREIGN KEY (`business_system_role_id`) REFERENCES `business_system_roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Before Migration

CREATE TABLE `business_system_role_location_type` (
  `business_system_role_id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `location_type` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`business_system_role_id`,`location_type`),
  CONSTRAINT `business_system_role_loc_type_role_id_fk` FOREIGN KEY (`business_system_role_id`) REFERENCES `business_system_roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

So as you can see, I have ran a migration and removed the location_type field and replaced it with a foreign key to a new location_types table. Both the business_system_role_id and location_type are set to UNIQUE PRIMARY KEY.

This all appears to work fine with MySQL but as soon as I attempt to run any of my tests (using SQLite) it stops working and complains: Illuminate\Database\QueryException : SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: business_system_role_location_type.location_type (SQL: insert into "business_system_role_location_type" ("business_system_role_id", "location_type_id") values (bb2051c2-1b5c-498d-bbcf-6dd9e20c4803, 38215722-bcba-4cac-8c83-fe867d8d8e65))

---

Question

Why am i getting a NOT NULL constraint for business_system_role_location_type.location_type when that column no longer exists? I have tried setting location_type to nullable->(true) before the migration, on the assumption it might update some SQLite setting, but this did not work.

I tried adjusting my code so that it performed $model->location_type = 'something' before $model->save(), and that worked... Even though the column does not exist. All references to it have been removed. I don't want to have to live with a workaround here and would like to get to the bottom of the reason for this error.

The Model looks as follows:

class BusinessSystemRoleLocationType extends Model
{
    protected $table = 'business_system_role_location_type';

    protected $fillable = [
        'business_system_role_id',
        'location_type_id',
    ];

    public $incrementing = false;

    public $timestamps = false;

    public function businessSystemRole(): BelongsTo
    {
        return $this->belongsTo(
            BusinessSystemRole::class,
            'business_system_role_id',
            'id'
        );
    }
}

Any help here would be greatly appreciated. :)

---

Edit - Migration

Here is the portion of the migration which deals with this table:

        // Add location_type_id field to table
        Schema::table('business_system_role_location_type', function (Blueprint $table) {
            // Must be nullable until it is populated with data
            $table->uuid('location_type_id')
                ->nullable()
                ->after('business_system_role_id');
        });

        // Assign location_type_id value to all entries
        BusinessSystemRoleLocationType::all()->each(function ($businessSystemRoleLocationType) {
            $locationTypeDataSetIndex = \array_search(
                $businessSystemRoleLocationType->location_type,
                \array_column($this->locationTypeDataSet, 'existsAs'),
                true
            );
            if ($locationTypeDataSetIndex !== false) {
                $newLocationTypeData = $this->locationTypeDataSet[$locationTypeDataSetIndex];
                $newLocationType = LocationType::whereSlug($newLocationTypeData['slug'])->get()->first();
            } else {
                $newLocationType = LocationType::all()->first();
            }
            $businessSystemRoleLocationType->location_type_id = $newLocationType->id;
            $businessSystemRoleLocationType->save();
        });

        // Adjust primary index and add foreign keys, and drop location_type field from table
        Schema::table('business_system_role_location_type', function (Blueprint $table) {
            $table->dropForeign('business_system_role_loc_type_role_id_fk');
            $table->dropPrimary(['business_system_role_id', 'location_type']);
        });
        Schema::table('business_system_role_location_type', function (Blueprint $table) {
            // ATTEMPT TO SET FIELD TO NULLABLE BEFORE REMOVING IT, MAYBE THIS WILL FIX THE NOT NULL CONSTRAINT ERROR?
            $table->string('location_type')->nullable()->change();
        });
        Schema::table('business_system_role_location_type', function (Blueprint $table) {
            $table->foreign('location_type_id', 'business_system_role_loc_type_fk')
                ->references('id')
                ->on('location_types');
            $table->foreign('business_system_role_id', 'business_system_role_loc_type_role_id_fk')
                ->references('id')
                ->on('business_system_roles')
                ->onDelete('cascade');

            // Now set not nullable UUID (Doctrine (change()) does not support UUID type)
            $table->string('location_type_id', 36)->change();

            $table->primary(['business_system_role_id', 'location_type_id'], 'business_system_role_loc_type_pk');

            $table->dropColumn('location_type');
        });

---

Edit 2 - Solution

I am editing here to provide my solution - though I will leave this open as others will most likely provide a better answer.

From what I can understand in order to remove constraints in an SQLite database it is recommended to delete the table and recreate it. (See here: https://stackoverflow.com/a/4007086/9675332). It appears that SQLite saves these constraints somewhere and doesn't actually remove them just because you remove the column. I really did not want to go down this route though, so here is what i did:

Solution 1: I modified my migration to set the field to have a default value before removing it, and this did pass the test (though it then subsequently failed on the UNIQUE constraint so it's not a working solution in my case, but may well work for others!)

Solution 2: Probably what I should have done to begin with actually. I simply renamed the column from location_type to location_type_id and manually set it to char(36). This appears to have updated everything in the background along with it and it now passes the tests.