On the first day of school, before the class list has a single name, the class teacher first draws its lines. She decides the columns: No, Name, Grade, City. The No column needs no thought: each new number continues from the last one. Name must be filled in, Grade and City can be filled in later. Grade always holds a number, Name and City always hold text.
In a database, that empty sheet is a table, and the command that draws it is CREATE TABLE. The command sets the table's name, its columns, the type of each column (the kind of value it holds: a number or text), and any extra rules. Every student recorded later becomes one row. The pattern for each column never changes: the column's name, its type, its rules if there are any, then a comma before the next column. Most errors in this lesson appear when that pattern is not followed.
The shape of CREATE TABLE
This students table is used until the end of the SQL track:
CREATE TABLE students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
grade INTEGER,
city TEXT
);
CREATE TABLE students (creates a table namedstudents. The list of its columns goes inside the parentheses.id INTEGER PRIMARY KEY AUTOINCREMENT: theidcolumn holds whole numbers, is the primary key, and is filled in by the database itself.name TEXT NOT NULL: thenamecolumn holds text and must be filled in.grade INTEGERandcity TEXT: two columns with no extra rules, so they may be empty.- Each column is written in this order: name, then type, then rules (if any), all on the same line. Commas separate the columns, and the last column gets no comma.
);closes the column list and the command.
Rules like PRIMARY KEY and NOT NULL are called constraints. The database checks the constraints every time data comes in.
Data types
| Type | Holds | Example |
|---|---|---|
INTEGER |
whole numbers | 90, -5 |
TEXT |
text | 'Dewi', 'London' |
REAL |
decimal numbers (with a point) | 87.5 |
Those are SQLite's three main types. You write text inside single quotes, like 'Dewi'. MySQL has VARCHAR(n) for text of at most n characters, for example name VARCHAR(100). SQLite accepts the spelling VARCHAR(100) too, but treats it like TEXT and does not check the length.
SQLite is also not strict about types: the text 'eighty' is still accepted in an INTEGER column. MySQL (with its default settings) and PostgreSQL refuse it. So pick the right type from the start.
If you want SQLite to be that strict, write STRICT after the closing parenthesis of the column list: ); becomes ) STRICT; (SQLite 3.37 and later). The text above is then refused with cannot store TEXT value in INTEGER column students.grade.
SQLite has no special type for dates. Store a date as TEXT in year-month-day order, such as '2026-11-15'. This form sorts correctly and can be calculated with: date('2026-11-15', '+7 days') gives 2026-11-22. The form '15/11/2026' is not recognised, so date('15/11/2026', '+7 days') gives an empty value (NULL). date('now') uses UTC time, so for some hours of the day it can differ from the date where you live. MySQL has the types DATE and DATETIME, PostgreSQL has date and timestamptz.
PRIMARY KEY and AUTOINCREMENT
Say two students, Dewi and then Rizky, are stored without giving an id. Before you look at the table below, guess: if the column is only id INTEGER, with no constraint at all, what is Dewi's id?
The result depends on how the id column is written:
The id column is written |
Dewi's id | Rizky's id |
|---|---|---|
id INTEGER |
NULL | NULL |
id INTEGER PRIMARY KEY |
1 | 2 |
id INTEGER PRIMARY KEY AUTOINCREMENT |
1 | 2 |
NULL means empty, no value at all. Nothing fills an INTEGER column that has no constraint, so Dewi's id is NULL, not 1.
PRIMARY KEYmakes the column the primary key: its value is unique. In SQLite, a column written exactly asINTEGER PRIMARY KEYis also filled automatically, with the largestidin the table plus 1.AUTOINCREMENTadds one guarantee: a number that was used once is never used again. WithoutAUTOINCREMENT, if the row with the largestid(say 3) is deleted, the next row gets 3 again. WithAUTOINCREMENT, the next row gets 4.- That guarantee costs extra work, so the SQLite documentation recommends plain
INTEGER PRIMARY KEY, unless old numbers must never be used again.
Each database program has its own spelling for this automatic id column. In the code below, a line that starts with -- is a comment: a note for the reader, which the database skips.
-- SQLite
id INTEGER PRIMARY KEY AUTOINCREMENT
-- MySQL
id INT PRIMARY KEY AUTO_INCREMENT
-- PostgreSQL
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
The SQL editor on BelajarWeb.dev translates the common MySQL spellings (INT, VARCHAR(n), AUTO_INCREMENT) into SQLite's spellings before it runs your command. So in the exercise, both spellings are accepted.
NOT NULL: a required column
A column marked NOT NULL must be filled in. A new row that leaves it empty is refused. Because the name column is written name TEXT NOT NULL, storing a student with no name is answered with this error:
Error: NOT NULL constraint failed: students.name
The row does not go into the table. NULL is not the same as the empty text '' (two quotes with nothing between them) or the number 0. Both are still values, so NOT NULL accepts them.
⚠️ Commas, where constraints go, and INT that isn't INTEGER
- A comma after the last column.
city TEXT,followed by);is answered withnear ")": syntax error. - A missing comma between columns.
id INTEGER PRIMARY KEY AUTOINCREMENT name TEXTis answered withnear "name": syntax error. - A constraint at the end of the list.
name TEXT, NOT NULLis answered withnear "NOT": syntax error. A constraint goes right after the type, on its column's own line, with no comma before it. - Thinking
INT PRIMARY KEYequalsINTEGER PRIMARY KEYin real SQLite. In SQLite, only the exact spellingINTEGER PRIMARY KEYmakes theidfill automatically. The editor here translatesINTintoINTEGER, but the SQLite on your own laptop does not.
TL;DR: CREATE TABLE name (column TYPE RULES, ...); creates a new table. Each column is written on one line: a name, a type (the kind of value it holds), then constraints (extra rules) if there are any. SQLite's main types are INTEGER (whole numbers), TEXT (text) and REAL (decimal numbers). MySQL, another database program, also has VARCHAR(n) (text of at most n characters). Put a comma between columns, but none after the last column. PRIMARY KEY makes that column's values unique, and in SQLite the database fills an INTEGER PRIMARY KEY column by itself. AUTOINCREMENT (in MySQL: AUTO_INCREMENT) guarantees that a used id number is never reused. NOT NULL makes a column required: a row that leaves it empty (NULL) is refused with the error NOT NULL constraint failed.
⚡ Draw the table
Write:
CREATE TABLE students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
grade INTEGER,
city TEXT
);
Meaning: CREATE TABLE students creates a new table named students. Each column is written on one line: a name, a type (the kind of value it holds), then its rules if there are any. For example, name TEXT NOT NULL is the name column: it holds text and must be filled in. Commas separate the columns, and the last column gets no comma.
⚡ Data types
Meaning: INTEGER holds whole numbers like 90. TEXT holds text, which you write inside single quotes, like 'Dewi'. REAL holds decimal numbers with a point, like 87.5. MySQL, another database program, also has VARCHAR(n) for text of at most n characters.
⚡ An id that fills itself
Guess first: the column is written only as id INTEGER, with no rules. A student called Dewi is stored without giving an id. What is her id?
NULL, which means empty. Nothing fills an INTEGER column that has no rules. Only with id INTEGER PRIMARY KEY does SQLite fill it by itself: 1 for the first row, 2 for the second.
Meaning: PRIMARY KEY makes the id column the primary key. In SQLite, the database also fills an INTEGER PRIMARY KEY column by itself. AUTOINCREMENT adds one guarantee: an id number that was used once is never used again.
⚡ NOT NULL: required
Meaning: NOT NULL means a column with this rule may not be NULL (empty). The name column is written name TEXT NOT NULL, so a student with no name is refused with the error NOT NULL constraint failed: students.name. The empty text '' and the number 0 are still accepted, because both are values.