Skip to main content

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​

a signed INT runs out at 2,147,483,647, a MEDIUMINT at 8,388,607 and a SMALLINT at 32,767. 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.

Deliberately blind to how big the table is​

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.

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.