How to Workaround Unsigned Integer for SQLite on Laravel

Here’s my setup: I use MySQL for dev + production and SQLite for testing.

If you use a similar setup and your codes depend on MySQL’s unsigned property to store only positive numbers, then you would notice during testing that SQLite does not actually work for unsigned.

The unsigned() flag would get ignored in Laravel and you would get a normal signed integer column in SQLite instead…. together with negative numbers.

Considering that SQLite does not natively support unsigned integer data type, it is unfortunately the expected result. If we really needed unsigned integer for positive numbers, how could we work around this in Laravel Migration?

Enters CHECK CONSTRAINT.

What is a Check Constraint?

It works like a database trigger. When a value is modified, it would be checked against the constraint before letting it pass through. So in this case, we want to define a constraint that checks that the value is always larger or equal to 0; and rejects when it is not.

How to Create a Check Constraint in Laravel Migration?

Assuming that we have a users table with a balance column that must never drops below zero.

When it’s not a SQLite database, we would include the column in our database migration as usual. But when it is SQLite, we need to first exclude the column in the table creation, then add the column afterwards.

This is because SQLite does not allow modifying columns so if you created the column via the usual migration step, you would not be able to alter it and add the CHECK CONSTRAINT as desired.

Sample codes:

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->bigIncrements('id');
        if (config('database.default') != 'sqlite') {
            $table->integer('available')->default(0)->unsigned();
        }
    });
    if (config('database.default') == 'sqlite') {
        \DB::statement('ALTER TABLE users ADD COLUMN balance INTEGER NOT NULL DEFAULT 0 CHECK(balance >= 0)');
    } else {
        $table->integer('available')->default(0)->unsigned();
    }
}

How to Create a Check Constraint in SQLite?

If you are not using Laravel, here’s how to create the check constraint.

During table creation:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    balance INTEGER NOT NULL DEFAULT 0 CHECK(balance >= 0),
)

Adding a new column with check constraint:

ALTER TABLE users ADD COLUMN balance INTEGER NOT NULL DEFAULT 0 CHECK(balance >= 0)

That’s all!