DataslopeCreating Tables in SQLite
Define SQLite tables with columns, types, primary keys, NOT NULL rules, and default values.
A database table is a place to store one kind of thing: books, customers, recipes, tasks, or anything else you want to remember.
Before a table can hold rows, you define its shape. In SQL, that job belongs to CREATE TABLE.
The basic CREATE TABLE shape
To create a table, give SQLite a table name and a list of columns. Each column has a name and a type.
The SELECT returns no rows because the table is empty. That is still useful: it proves the table exists and shows the column names.
Read the statement as: create a table called books with an integer id, text title, text author, and integer year.
From statement to grid
The table definition becomes an empty grid. The columns are ready; rows come later with INSERT.
| id | title | author | year |
|---|---|---|---|
Choosing SQLite column types
SQLite is beginner-friendly, but it has an important difference from many other databases: it uses dynamic typing. That means SQLite mostly cares about the value you store, not only the type written on the column.
Still, you should choose clear types because they document your intent and help SQLite apply type affinity.
| Type | Use it for |
|---|---|
INTEGER | Whole numbers like counts, ids, and years |
TEXT | Words, names, emails, and dates written like '2025-01-15' |
REAL | Decimal numbers like prices, weights, and measurements |
NUMERIC | Values that should behave like numbers, often money-like or yes/no 0 and 1 values |
SQLite type affinity
SQLite will try to store values in a sensible way based on the column's declared type, but it is more flexible than many server databases. Use the right type anyway: it makes your table easier for humans to understand.
Which SQLite type is the clearest choice for a person's name?
INTEGER
TEXT
REAL
NUMERIC
INTEGER PRIMARY KEY
Most tables need a column that uniquely identifies each row. In SQLite, the everyday beginner pattern is:
id INTEGER PRIMARY KEYThat column becomes a unique row id. If you omit it during an insert, SQLite can assign the next id for you.
Notice that the INSERT statement did not provide id values. SQLite filled them in because id is an INTEGER PRIMARY KEY.
NOT NULL and DEFAULT
A table can include rules called constraints. Two beginner-friendly constraints are:
NOT NULL: this column must have a value.DEFAULT: if the insert omits this column, use a fallback value.
The rows have times_done set to 0 even though the insert did not mention that column. The DEFAULT 0 rule filled it in.
A column is declared times_done INTEGER DEFAULT 0. What happens when an insert omits times_done?
The table is deleted.
The row is always rejected.
SQLite stores 0 for that column.
SQLite stores the table name there.
Practice challenge
Create a table named notes with these columns:
id INTEGER PRIMARY KEYbody TEXT NOT NULLpinned INTEGER DEFAULT 0
Then select all columns from notes.
Check your understanding
What does CREATE TABLE define?
The order in which every query must run.
A table's name, columns, types, and simple rules.
The exact rows that must always be returned.
A backup copy of the database.
Why is id INTEGER PRIMARY KEY useful in SQLite?
It stores long paragraphs better than TEXT.
It prevents the table from having any rows.
It gives each row a unique identifier and can be auto-assigned when omitted.
It makes every column use decimal numbers.
What does SQLite's dynamic typing mean for beginners?
Column types are useless and should never be written.
SQLite has the same strict type behavior as every other database.
SQLite is flexible about stored values, but you should still choose clear column types.
TEXT columns automatically become primary keys.