How to Add Unsigned Integer Column in SQLite
Jian Jye • November 17, 2019sqlite
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?
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?
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.
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.