CREATE TABLE is where you stop describing data and start constraining it. Every column you declare carries a type, a nullability rule and often a default, and the engine stores that declaration and enforces it on every row that ever enters the table. Getting the types right is the cheapest performance and correctness work you will ever do.
How does CREATE TABLE choose the right types?
The tea shop in this module sells cast iron pans, mugs and kettles. Four tables hold the business: customers, products, orders and the lines inside each order. Here is the products table as it was created:
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
sku TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
category TEXT NOT NULL,
price REAL NOT NULL,
stock INTEGER NOT NULL DEFAULT 0,
added_on TEXT
);
The catalogue loads seven products, and the SKU is what the packing list quotes:
INSERT INTO products (product_id, sku, name, category, price, stock, added_on) VALUES
(1, 'TB-001', 'Cast Iron Pan', 'cookware', 42.5, 18, '2025-01-14'),
(2, 'TB-002', 'Enamel Mug', 'drinkware', 9.95, 120, '2025-02-03'),
(3, 'TB-003', 'Bamboo Tray', 'serving', 24.0, 35, '2025-03-11'),
(4, 'TB-004', 'Glass Carafe', 'drinkware', 31.75, 22, '2025-04-08'),
(5, 'TB-005', 'Walnut Serving Board', 'serving', 58.0, 12, '2025-05-19'),
(6, 'TB-006', 'Copper Kettle', 'cookware', 38.0, 8, '2025-06-24'),
(7, 'TB-007', 'Linen Tea Towel', 'textiles', 14.5, 60, '2025-07-02');
Read each clause as a promise. INTEGER says the column holds whole numbers. NOT NULL says a row without a value is not a legal row. UNIQUE says no two rows may share a SKU. DEFAULT 0 says a row that says nothing about its stock has zero of it. The engine keeps that definition and can hand it back to you:
SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'products';
sql
----------------------------------------
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
sku TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
category TEXT NOT NULL,
price REAL NOT NULL,
stock INTEGER NOT NULL DEFAULT 0,
added_on TEXT
)
The same information is available column by column, and the metadata view is the first place to look when you are handed an unfamiliar schema:
PRAGMA table_info(products);
cid name type notnull dflt_value pk
--- ---------- ------- ------- ---------- --
0 product_id INTEGER 0 NULL 1
1 sku TEXT 1 NULL 0
2 name TEXT 1 NULL 0
3 category TEXT 1 NULL 0
4 price REAL 1 NULL 0
5 stock INTEGER 1 0 0
6 added_on TEXT 0 NULL 0
The notnull and dflt_value columns are the machine-readable form of the promises above, and the pk column flags the primary key. In MySQL the equivalent is DESCRIBE products, in PostgreSQL it is \d products, and in SQL Server it is sp_help products — different spellings, same information.
What do the defaults decide for you?
A default is a promise about the shape of the data that saves the caller from having to supply it. The roasters table declares a boolean-ish flag with a default of 1:
CREATE TABLE roasters (
roaster_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
country TEXT,
founded INTEGER,
active INTEGER NOT NULL DEFAULT 1
);
INSERT INTO roasters (roaster_id, name, country, founded, active) VALUES
(1, 'Kettle & Co', 'UK', 1998, 1),
(2, 'Copper Ridge Roasters', 'Peru', 2011, 1),
(3, 'Cast Iron Collective', 'Japan', 2004, 0);
roaster_id name country founded active
----------- ----------- ------- ------- ------
1 Kettle & Co UK 1998 1
The insert said nothing about active, and the row came out active. That is the whole point of a default: the common case does not need a value, and the exception does not need a special code path. Booleans are a special case worth noticing — SQLite has no boolean type, so a flag is an INTEGER constrained to 0 or 1, and MySQL's tinyint(1), PostgreSQL's boolean and SQL Server's bit are the equivalents.
How many types do you actually need?
SQLite recognises five storage classes and maps everything onto them, which is a useful reminder that the detailed types in other engines are refinements of a small set:
| Storage class | Holds | Typical use |
|---|---|---|
| INTEGER | whole numbers | identifiers, quantities, counts, years |
| REAL | floating point numbers | prices, weights, measurements |
| TEXT | character strings | names, emails, dates stored as ISO text |
| BLOB | raw bytes | images, files, encrypted payloads |
| NUMERIC | numbers with mixed precision | exact decimal arithmetic |
Other engines are more expressive: PostgreSQL has smallint, integer, bigint, numeric, real, double precision, varchar, text, char, date, timestamp, timestamptz, boolean, uuid and jsonb; MySQL adds tinyint, mediumint, decimal, date, datetime and year; SQL Server offers tinyint, smallint, int, bigint, decimal, money, date, datetime2, uniqueidentifier and more. The names differ and the widths differ, but the decisions are the same three: is the value a whole number, a fraction, or text — and does it need a fixed length?
What does the engine actually store?
The declared type is a rule, not a cage, in SQLite. The declared type creates an affinity, and a value that can be converted losslessly is converted on the way in:
CREATE TABLE probe (id INTEGER, label TEXT, price REAL, quantity INTEGER);
INSERT INTO probe VALUES (1, 'kettle', 42, 7);
SELECT id, label, price, typeof(price) AS price_type, quantity, typeof(quantity) AS qty_type FROM probe;
id label price price_type quantity qty_type
-- ------ ----- ---------- -------- --------
1 kettle 42.0 real 7 integer
The integer 42 became the real 42.0, because a REAL column has real affinity and the conversion is lossless. The opposite case is more interesting:
INSERT INTO probe VALUES (2, 'text price', 'not a number', 'seven');
SELECT id, label, price, typeof(price) AS price_type, quantity, typeof(quantity) AS qty_type FROM probe;
id label price price_type quantity qty_type
-- ---------- ------------ ---------- -------- --------
1 kettle 42.0 real 7 integer
2 text price not a number text seven text
'not a number' stayed text in a REAL column, and 'seven' stayed text in an INTEGER column, because neither could be converted without losing information. SQLite's dynamic typing kept the honest value rather than destroying it. A stricter engine — PostgreSQL, MySQL in strict mode, SQL Server — would have rejected both inserts outright. That difference is why a schema that works in SQLite can surprise you on a server, and why lesson 39 looks closely at the type choices.
Quick reference
- CREATE TABLE declares type, nullability and default for every column, and the engine enforces all three.
- NOT NULL makes a missing value impossible; DEFAULT supplies the value when one is not given.
- PRAGMA table_info (or DESCRIBE, or \d) shows the declared schema column by column.
- SQLite has five storage classes and applies affinity rather than strict typing.
- A value that cannot be converted is kept as text in SQLite and rejected in stricter engines.
Design drill: declare a table for the shop's suppliers with an identifier, a name that must exist, a country, a founding year and an active flag that defaults to active — then use the metadata view to confirm that each promise you wrote down is actually in the schema.