CREATE TABLE is where you stop describing the data and start constraining it. Every column you declare carries a type, a nullability rule and often a default. The engine stores that declaration, and it 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.
This is the first lesson in the Table Design and Schema module. The next lesson in this module is "VARCHAR vs TEXT vs INT vs DATE: Which Type Fits?".
How does the CREATE TABLE choose the right types?
The tea shop in this module sells cast iron pans, mugs and kettles. Four tables hold the business: the customers, the products, the orders, and the lines inside each order. Every fact about the shop lives in one of those four tables, and every rule about those facts is written down as a column definition.
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. The code that inserts those rows does not have to repeat the shape of a product, because the table already carries the shape.
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. The INTEGER says the column holds whole numbers. The NOT NULL says a row without a value is not a legal row. The UNIQUE says no two rows may share a SKU. The DEFAULT 0 says a row that says nothing about its stock has zero stock. A promise like these is checked by the engine on every write, so you never have to trust that your own code remembered to follow it.
The engine keeps that definition, and it 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 0The notnull and dflt_value columns are the machine-readable form of those promises, 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. They are different spellings of the same information, so a schema written on one engine can still be read on another.
What do the defaults decide for you?
A default is a promise about the shape of the data, and it saves the caller from having to supply it. The caller sends the fields it cares about, and the engine fills in the rest.
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);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. MySQL's tinyint(1), PostgreSQL's boolean and SQL Server's bit are the equivalents. Whichever name you use, the flag still holds only two possible values.
How many types do you actually need?
SQLite recognises five storage classes and maps everything onto them. That is a useful reminder that the detailed types in the other engines are refinements of a small set. You do not have to learn forty type names before you can build a working schema.
| 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 |
The 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? Answer those three questions and the type name writes itself.
What does the engine actually store?
In SQLite the declared type is a rule, not a cage. The declared type creates an affinity, and a value that can be converted without any loss 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 integerThe 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 textThe 'not a number' stayed text in a REAL column, and the 'seven' stayed text in an INTEGER column, because neither of them could be converted without losing the information.
SQLite's dynamic typing kept the honest value instead of destroying it. A stricter engine, such as PostgreSQL, MySQL in strict mode or SQL Server, would have rejected both of those inserts outright.
That difference is why a schema that works in SQLite can surprise you on a server, and it is why lesson 39 looks closely at the type choices.
Quick reference
CREATE TABLEdeclares the type, the nullability and the default for every column, and the engine enforces all three.NOT NULLmakes a missing value impossible, andDEFAULTsupplies the value when one is not given.PRAGMA table_info, orDESCRIBE, or\d, shows the declared schema column by column.- SQLite has five storage classes, and it applies affinity rather than strict typing.
- A value that cannot be converted stays text in SQLite, and it is rejected by the 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 really is in the schema.