MY.L6.PK_NOT_BIGINT — An integer primary key too narrow to grow into

Category: idiom
Level: 6
Confidence: deterministic
Downtime class: none
Stability: stable
Suites: audit
Applies to: MySQL 8.4
Not transferable to MariaDB: this page describes MySQL 8.4 behavior. MariaDB answers the same driver and does not share these semantics, so SQLens refuses it outright rather than reasoning about it — see drivers/unsupported.

The timing is the whole problem

An INT runs out at 2,147,483,647 signed and 4,294,967,295 unsigned, a MEDIUMINT at 8,388,607 and 16,777,215, a SMALLINT at 32,767 and 65,535. increments() gives you INT UNSIGNED, so the example below is near the second number, not the first — and the finding names whichever applies to the key it found. Nothing warns on the way there. The first symptom is an INSERT failing on a table that has been working for years.

And the moment the fix becomes necessary is precisely the moment it is most expensive: widening the key rewrites the table and every index over it, under a lock, on the largest table you have.

Chosen early, the same decision costs four bytes a row. That asymmetry is the entire argument, and it is why this sits at level 6 — an appetite for being told now, not a defect to fail a build on.

Flagged

Schema::create('events', function (Blueprint $table) {
    $table->increments('id');
});

Preferred

Schema::create('events', function (Blueprint $table) {
    $table->id();
});

Widen the referencing columns in the same change

A key widened on its own is half a fix: every foreign-key column pointing at it has to widen too, or the constraint stops matching.

Those columns live on other tables, and an audit judges one object at a time — so this finding says they must be widened without naming them. Claiming to enumerate them from here would be a promise the rule cannot keep.

No row estimate, no escalation by size. Statistics belong to the deploy suite, where a number that changes between two runs is expected. An audit that read them would report differently on Tuesday than on Monday over an unchanged schema — and determinism is not a property to trade for a sharper heading.

The framework's own migrations ledger is exempt

Laravel creates migrations itself, from DatabaseMigrationRepository, with increments('id') — on every migrate:install and regardless of what the application would prefer. The advice this rule gives cannot be taken there: the table belongs to the framework, and rewriting it would mean writing against the framework's own default on a table the application does not own.

So the rule skips it, and it is the only table it treats that way. The name comes from database.migrations.table rather than from a constant here — rename the ledger and an ordinary table called migrations is judged like any other.

What is not flagged

A bigint key, a UUID or text key, and any composite key. A composite key's range is the product of its parts, so "too narrow" stops being a property of one column, and a rule reporting one anyway would be doing arithmetic nobody asked it to do.

A small lookup table will never approach the ceiling and gains nothing from widening. Ignore those deliberately rather than converting the whole schema:

'audit' => [
    'ignore' => [
        'pairs' => [
            ['rule' => 'MY.L6.PK_NOT_BIGINT', 'objects' => ['shop.currencies']],
        ],
    ],
],

Sources

MySQL 8.4 reference: integer types — the range table: TINYINT to 127 signed, SMALLINT to 32,767, MEDIUMINT to 8,388,607, INT to 2,147,483,647, BIGINT to 9,223,372,036,854,775,807. Unsigned doubles each ceiling, which postpones the problem rather than removing it.

The timing is the whole problem​

Flagged​

Preferred​

Widen the referencing columns in the same change​

Deliberately blind to how big the table is​

The framework's own migrations ledger is exempt​

What is not flagged​

Sources​