How to Workaround Unsigned Integer for SQLite in Laravel Migration cover image

How to Workaround Unsigned Integer for SQLite in Laravel Migration

Jian Jye • July 30, 2019

laravel sqlite

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.

NOTE: If you are looking for a SQLite specific article, you may refer to How to Add Unsigned Integer Column in SQLite instead.


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');

        // Create the unsigned integer column in MySQL as usual
        if (config('database.default') != 'sqlite') {
            $table->integer('balance')->default(0)->unsigned();
        }
    });

    // Add the column separately if it's SQLite
    if (config('database.default') == 'sqlite') {
        \DB::statement('ALTER TABLE users ADD COLUMN balance INTEGER NOT NULL DEFAULT 0 CHECK(balance >= 0)');
    }
}

How to Create a Check Constraint in SQLite?

If you want to test check constraint directly with SQLite, here's how to create it.

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!

Sign up for our newsletter