SQL chapters

Chapter 5 of 17

SQLite values, affinity and constraints

Try this in our SQLite Compiler →

The idea

SQLite has NULL, integer, real, text and blob storage classes. Declared column types influence affinity rather than behaving exactly like every statically typed database. Constraints such as NOT NULL, CHECK and UNIQUE express data rules. Do not assume a VARCHAR length enforces a maximum in SQLite.

Worked example

DROP TABLE IF EXISTS scores;
CREATE TABLE scores (name TEXT NOT NULL, score INTEGER CHECK(score BETWEEN 0 AND 100));
INSERT INTO scores VALUES ('Asha', 82);
SELECT name, typeof(score) AS storage_type FROM scores;

What to check

The score's storage type is integer. The CHECK constraint rejects a score outside 0–100; this is different from displaying a warning in an application.

Practice task

Try a score of 101 in the disposable table and inspect the real constraint error. Restore a valid value afterward.

Check your understanding

Explain the result in your own words before changing the example. Change one thing at a time, compare the result with your prediction, and keep the version that demonstrates the concept clearly.

Try it yourself

Output
Run your project to see output here.
Ctrl/Cmd+Enter runs · Ctrl/Cmd+S downloads the project · Local auto-save is always on