splitbrain's avatar

sqlite migration is weird

I have the following database table in a sqlite database:

sqlite> .schema owners
CREATE TABLE IF NOT EXISTS "owners" ("id" integer primary key autoincrement not null, "name" varchar not null, "email" varchar not null, "phone" varchar, "address" varchar, "tos" text not null, "created_at" datetime, "updated_at" datetime, "bank_account" varchar not null default '', "is_company" tinyint(1) not null default '0');
CREATE UNIQUE INDEX "owners_name_unique" on "owners" ("name");
CREATE UNIQUE INDEX "owners_email_unique" on "owners" ("email");

Because I want to do some restructuring, I need to increase the ID of each entry by 1000. This works fine in sqlite itself:

sqlite> UPDATE owners SET id = id + 1000;

However when I try to do the same in a migration:

    public function up(): void
    {
        DB::unprepared('UPDATE owners SET id = id + 1000');
    }

I get a foreign key error:

2024_11_23_133716_increaseownerids .................................................................................... 0.82ms FAIL

   Illuminate\Database\QueryException 

  SQLSTATE[23000]: Integrity constraint violation: 19 FOREIGN KEY constraint failed (Connection: sqlite, SQL: UPDATE owners SET id = id + 1000)

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:825
    821▕                     $this->getName(), $query, $this->prepareBindings($bindings), $e
    822▕                 );
    823▕             }
    824▕ 
  ➜ 825▕             throw new QueryException(
    826▕                 $this->getName(), $query, $this->prepareBindings($bindings), $e
    827▕             );
    828▕         }
    829▕     }

      +7 vendor frames 

  8   database/migrations/2024_11_23_133716_increaseownerids.php:17
      Illuminate\Support\Facades\Facade::__callStatic()
      +26 vendor frames 

  35  artisan:13
      Illuminate\Foundation\Application::handleCommand()

I have absolutely no clue why that should be. There is no foreign key on that table!? What's going on here?

0 likes
1 reply
splitbrain's avatar
splitbrain
OP
Best Answer
Level 1

Ahhh of course, seconds after posting I understand. I have other tables that reference the owners.id as foreign key. When running on the command line, foreign key checking is off by default.

Please or to participate in this conversation.