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.

The CREATE TABLE statement you show is not compatible with sqlite. The fact that you mention MySQL, but the SQL is not valid for sqlite indicates to me that the "migration" with the sqlite schema / code did not go as you think it did. Even stronger evidence is that you can insert data into the field and get a NULL error for the field. Despite what you think, the field is still there. You mention "migration" numerous times, but you didn't share any details about how this migration was done. Go back and check the migration status / log / code and also inspect the sqlite DDL (CREATE TABLE). — C Perkins, Oct 14 '19 at 16:25
@CPerkins the statement was generated from an export script from my database manager. I figured it was easy to show the structure this way. I have added the section of the migration file which deals with this table, including my attempt to make nullable the field before removing it. If i copy the error responses' SQL and run it in MySQL (the production database type) it works fine, but does not in the testing which uses SQLite. The tests use the `refreshDatabase` trait which runs all migrations on each setUp. https://laravel.com/docs/5.7/database-testing#resetting-the-database-after-each-test — TPHughes, Oct 15 '19 at 07:09
Glad that you found a working solution, but the appropriate thing to do is add the solution as an actual answer. Yes, it is okay to answer your own question. Answering it does not "close" the question. If you mean to leave it unanswered so that it remains in the unanswered queue, it may never happen and so your question will go unanswered and will eventually be deleted. — C Perkins, Oct 15 '19 at 15:55
Showing the structure (schema) of the DB by posting `CREATE TABLE` statement is just fine and is in fact a preferred way to share table details. The concern was that you were having problems with sqlite database, yet the `CREATE TABLE` was for mySQL (e.g. it had `ENGINE=InnoDB` and other non-sqlite syntax), so it makes one wonder if you really checked the sqlite schema. I would have expected such a statement from before and after the migration for the *sqlite table directly*. It is somewhat useful to know it worked in mySQL, but that's not where the problem was so it wasn't as useful. — C Perkins, Oct 15 '19 at 16:03
You say "It appears that SQLite saves these constraints somewhere and doesn't actually remove them...", but you have it backwards. Sqlite column constraints are dropped when the column is dropped. The migration script apparently uses eloquent data-object mapper code, which implies that an additional model is generated in your code that maps to the actual DB tables. So really you should say "It appears that laravel/eloquent model stores the column separately so that when it is dropped and *an error occurs*, the laravel model was updated even though the actual DB column was not removed." — C Perkins, Oct 15 '19 at 16:16
Honestly, I find it interesting that you did not get an error until trying to run application code. Really, the migration code should have failed when attempting to the drop the sqlite column. It is troublesome that it update the code data model when the actual DB schema failed an update. Technically, the laravel/eloquent module could have been smart enough to execute an sqlite SQL script with necessary steps to remove and recreate the table, but it apparently did not do that. Does the migration process not a have a log or anything that would indicate such an error? — C Perkins, Oct 15 '19 at 16:22
Sorry for going on and on, but I just realized that renaming a column and changing its type in sqlite would likewise require the same operation on the table as you mentioned: rename, create new, copy data, delete old. (That cannot be done with a simple ALTER TABLE command in sqlite.) The fact that your new migration can handle that correctly for sqlite indicates that it really should have been able to handle it for the `dropColumn` command. So I would consider that a bug in the database manager and/or eloquent implementation for sqlite. — C Perkins, Oct 15 '19 at 16:29

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

0 Answers0