If you're stuck with an older version of SQLite and/or want to enforce order on an existing table without creating a new table with STRICT and then copying all

If you're stuck with an older version of SQLite and/or want to enforce order on an existing table without creating a new table with STRICT and then copying all your rows over and/or also want to do things like enforce signedness, int size, or char/varchar length on a field like you can in other DBs, you can use CHECK constraints. CREATE TABLE users ( user_id CHAR(36) NOT NULL PRIMARY KEY CONSTRAINT user_id_length CHECK (LENGTH(user_id) = 36), email_address VARCHAR(255) UNIQUE CONSTRAINT email_address_length CHECK (email_address IS NULL OR LENGTH(email_address) < 256), role UNSIGNED TINYINT(1) NOT NULL CONSTRAINT role_valid CHECK (role >= 0 AND role <= 9) ) Note that the column types here are just to describe to the user what the field should be doing and it's the constraints that actually enforce it. Behind the scenes SQLite still creates two "text (supposedly but whatever)" and one "integer (supposedly but whatever)" columns. It's a little frustrating that all this extra cruft is necessary to get the world's most popular RDBMS to take data correctness seriously. I hope that some SQLite fork that behaves more like other RDBMSes when it comes to this stuff catches on some day, but the fact that that hasn't happened yet makes me think that the demand isn't there, somehow, unfortunately. https://sqlite.org/lang_createtable.html#ckconst https://sqlite.org/lang_createtable.html#ckconst