Creating Tables — SQL

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…

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
);

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.

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

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.

What you will learn