SQLite General error 1: foreign key mismatch

I’ve been fighting this bug in a Laravel application I’m writing currently. It’s a greenfield project, a side project of mine, so I don’t have any legacy code to contend with, which means any bugs in the system are introduced by me. Turns out, this was one of them.

Out of the box Laravel will scaffold migrations on a bunch of tables and uses the ->id(); for the ids of whatever model lives in that table. That’s a short code for a UNSIGNED BIGINT AUTOINCREMENT kind of a column, the default for many tutorials.

I don’t like using them, because it opens up your application to enumeration vulnerabilities. A common alternative is to use uuidv4 as a primary key, but they can’t be sorted. The new uuidv8 type can, but not many things support it, so until then ULIDs are here to fill that particular niche of a sortable, unguessable primary key. Laravel has support for ULIDs, so all is well!

I dutifully replaced all the ->id(); calls in the migrations to ->ulid('id'), added the necessary HasUlids trait to the models, made sure that my own model was tied to the Users model via a foreign key constraint, refreshed migrations, and tried to create a new connected entity. Laravel said no:

SQLSTATE[HY000]: General error: 1 foreign key mismatch - "customers" referencing "users" (Connection: sqlite, SQL: insert into "customers" ("user_id", ...) values (01j4fwh0yr9eppk9vt7hxfbr29, ...);

When I tried this exact query — irrelevant details omitted for brevity, — the insert succeeded, SQLite didn’t complain, and I had a hard time understanding why. I’ll come back to this point in a moment.

What caused this?

Here are the relevant definitions in migrations:

Schema::create('users', function (Blueprint $table) {
    $table->ulid('id');
    $table->string('name');
    $table->string('email')->unique();
    ...
})

Schema::create('customers', function (Blueprint $table) {
    $table->ulid('id')->primary();
    $table->foreignUlid('user_id')
        ->constrained()
        ->restrictOnDelete();
    ...
});

If you’ve not spotted it yet, the issue is in the schema definition of the users table. The previous version, the one that Laravel gives you by default, looks like this:

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    ...
})

All I did was replace the ->id() with ->ulid('id'), thinking it would work.

The reason the foreign key mismatch happens is that in order to create a foreign key constraint, the column specified by the foreign key (users.id) needs to be unique! Usually the primary. Users table didn’t have a unique or primary constraint on the id, it has it on the email, which absolutely makes sense! You want to make sure that a single email address can only ever log into a single user account.

The solution was rather straightforward: add the ->primary(); modifier to the ->ulid('id') field of the users table:

Schema::create('users', function (Blueprint $table) {
    $table->ulid('id')->primary();
    $table->string('name');
    $table->string('email')->unique();
    ...
})

Why did SQLite execute the query directly?

Turns out when I connect to a SQLite database directly via a database program — PhpStorm in my case — it will behave differently by default to what Laravel does. In Laravel’s config/database.php file the SQLite driver has foreign key constraints turned on, whereas a direct connection does not.

Shout out to Len Woodward over at the Mastering Laravel community who told me I should run PRAGMA foreign_keys = ON; in the connection before running the above insert to surface the same issue. I didn’t understand what that was for at the time, but in retrospect it would have made debugging this easier. Check him out at his website, Twitter, and YouTube channels.