Data Types and Constraints

Chapter 7 moved rows around as though their contents did not matter. Chapter 8 of the SQL for Beginners series is about what a column will actually accept: SQLite’s five storage classes, the type names other engines insist on, and the constraints — NOT NULL, DEFAULT, UNIQUE, CHECK — that make the database reject bad data instead of discovering it three months later in a report.

Five storage classes

SQLite does not have a fixed list of column types; it has five storage classes, and every value you ever store belongs to exactly one. Ask SQLite directly:

SELECT typeof(499) AS whole, typeof(4.5) AS decimal, typeof('Ada') AS text, typeof(NULL) AS missing;

Expected output:

whole    decimal  text  missing
-------  -------  ----  -------
integer  real     text  null

That is the whole type system: 64-bit integer, 64-bit float real, text of any length, blob for bytes, and null for the absence of a value. The familiar SQL names you type in CREATE TABLE are hints, not laws, which is the next section’s point.

Declared types and affinity

Every column declares a type, and SQLite maps it onto one of five affinities that decide how incoming values are converted before storage. A column declared NUMERIC will turn '42' into the number 42 but leave 'hello' as text, because nothing sensible can be done with it:

CREATE TABLE notes (label TEXT, value NUMERIC);
INSERT INTO notes (label, value) VALUES ('whole', 42), ('decimal', 3.5), ('text', 'hello'), ('numeric text', '42');

SELECT label, value, typeof(value) AS storage FROM notes;

Expected output:

label         value  storage
------------  -----  -------
whole         42     integer
decimal       3.5    real
text          hello  text
numeric text  42     integer

Affinity converts where it can and stores what it is given otherwise. That forgiveness cuts both ways, and it is the single biggest difference between SQLite and a strict engine:

CREATE TABLE scratch (price NUMERIC);
INSERT INTO scratch VALUES ('free');
SELECT price, typeof(price) AS storage FROM scratch;

Expected output:

price  storage
-----  -------
free   text

SQLite accepts the word free in a price column without a murmur. MySQL and PostgreSQL would refuse the row outright — which is what you want, and which brings us to writing types that port.

Type names across SQLite, MySQL and PostgreSQL

Chapters 1–6 declared the sample tables with names that work everywhere, but each engine reads them differently. This is the cheat sheet to keep beside you:

Two details in that table are worth pausing on. SQLite has no date type, so signup_date is really a string that happens to be written in ISO form ('2026-01-12') so that sorting and comparison behave — the date functions in chapter 18 parse it. And SQLite ignores lengths: VARCHAR(50) is accepted, read as TEXT affinity, and never enforced. You can see what your own tables really declared:

PRAGMA table_info(products);

Expected output:

cid  name      type     notnull  dflt_value  pk
---  --------  -------  -------  ----------  --
0    id        INTEGER  0                    1
1    name      TEXT     1                    0
2    category  TEXT     1                    0
3    price     NUMERIC  1                    0
4    stock     INTEGER  1        0           0

A PRAGMA is SQLite’s own introspection command: cid is the column position, dflt_value shows the default for stock, and pk marks the primary key. MySQL answers the same question with DESCRIBE products; and PostgreSQL with \d products.

NOT NULL, DEFAULT and UNIQUE

Constraints are promises the database enforces on every write. Build a small table that keeps them all in view — note the REFERENCES clause, which stays dormant until chapter 9 switches foreign keys on:

CREATE TABLE reviews (
    id INTEGER PRIMARY KEY,
    book_id INTEGER NOT NULL REFERENCES products(id),
    rating INTEGER NOT NULL DEFAULT 5 CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    created_at TEXT NOT NULL DEFAULT '2026-01-01'
);

INSERT INTO reviews (book_id, rating, comment) VALUES (1, 4, 'Kept me up all night');
INSERT INTO reviews (book_id) VALUES (2);
SELECT id, book_id, rating, comment, created_at FROM reviews;

Expected output:

id  book_id  rating  comment               created_at
--  -------  ------  --------------------  ----------
1   1        4       Kept me up all night  2026-01-01
2   2        5                             2026-01-01

The second insert proves three things at once: columns without values took their DEFAULT (rating 5, the date), the free-text comment stayed NULL because nothing forbids it, and id was assigned automatically. Add UNIQUE to any column — email TEXT NOT NULL UNIQUE is how our customers table stops duplicate sign-ups — and violation attempts are rejected the same way, with an error naming the constraint.

CHECK: rules the database enforces

CHECK takes any expression that must be true for the row to be stored. It is how you keep ratings on a sensible scale and prices non-negative, without writing the same test in application code:

INSERT INTO reviews (book_id, rating) VALUES (NULL, 3);

Expected output:

Error: NOT NULL constraint failed: reviews.book_id

INSERT INTO reviews (book_id, rating) VALUES (3, 6);

Expected output:

Error: CHECK constraint failed: rating BETWEEN 1 AND 5

Both errors arrive as soon as the statement runs, and the statement changes nothing — that is the whole point of constraints. SQLite names the exact column or expression that failed, which is also how a violated foreign key reports itself once chapter 9 turns enforcement on. MySQL and PostgreSQL raise the same conditions with their own wording; the guarantee is portable even when the message is not.

Auto-generated ids

Because id is declared INTEGER PRIMARY KEY, it is an alias for SQLite’s hidden rowid: a 64-bit integer filled in automatically with one more than the largest id used so far, and directly addressable for fast lookups. Ask for the last one you created:

INSERT INTO reviews (book_id, rating) VALUES (4, 3);
SELECT last_insert_rowid() AS new_id;

Expected output:

new_id
------
3

One warning about reuse: without the optional AUTOINCREMENT keyword, deleting the highest row lets its id be handed out again, which surprises code that assumed ids only ever grow. Add INTEGER PRIMARY KEY AUTOINCREMENT to forbid the reuse — SQLite keeps a private table to remember every id ever issued. The equivalents elsewhere are AUTO_INCREMENT in MySQL and SERIAL or GENERATED ALWAYS AS IDENTITY in PostgreSQL, none of which reuse ids either.

Common mistakes

• Assuming VARCHAR(50) limits length in SQLite — the number is parsed and ignored; only a CHECK (LENGTH(x) <= 50) enforces it.
• Storing money as REAL — binary floats round 0.1 and 0.2 unevenly; keep rupees as integers or use a fixed-precision type on other engines.
• Writing dates as free text — store the ISO form '2026-01-12' so string ordering equals date ordering, and parse in the query.
• Leaving a column nullable by accident — omitting NOT NULL means NULL is always accepted; decide per column rather than by default.
• Trusting SQLite to reject nonsense silently — strict engines say no, SQLite stores it; validate in the schema with CHECK.
• Expecting constraints to fire when they are disabled — REFERENCES does nothing until PRAGMA foreign_keys is on, which is chapter 9’s subject.

Practice checklist

1. Run the typeof() query yourself, then add typeof(x'10') and explain the blob answer.
2. Create a scratch table with a NUMERIC column, insert 'free', and confirm SQLite stores it as text.
3. Use PRAGMA table_info() on orders and order_items, and write down which columns are marked notnull.
4. Build the reviews table, trigger one NOT NULL and one CHECK error, and read the messages aloud.
5. Insert two more reviews, predict each new id, and verify with last_insert_rowid().

What's next?

Types keep values sensible and constraints keep them present, but nothing yet ties one table to another — an order could still name a customer who does not exist. Chapter 9 adds the links in Keys and Referential Integrity.