How to Add Unsigned Integer Column in SQLite cover image

How to Add Unsigned Integer Column in SQLite

Jian Jye • November 17, 2019

sqlite

If you came from a MySQL background, you might be puzzle to find out that SQLite and many others like PostgreSQL actually do not have a unsigned integer data type.

The reason is because that unsigned integer is not actually part of the SQL standard, and so the other databases have less incentives to implement it accordingly despite it being quite popular in MySQL.

So if there is no such data type, how do we add an unsigned integer column in SQLite?

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 SQLite?

Creating check constraint in SQLite is pretty simple. 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)

For your information, if you are looking to modify an existing column to become unsigned, that would not be possible as check constraint is only applicable to new columns.

Your best bet would be to create a new column with the unsigned integer check constraint, then duplicate the values over instead.


Additional Link

There is another version of this article specifically for Laravel Migration when dealing with Unsigned Integer in SQLite, in case you are looking for it.

Sign up for our newsletter