Every database journey starts with CREATE TABLE. It’s the command that turns an empty database file into something structured — a place with defined columns, defined types, and rules about what kind of data is and isn’t allowed in. Get this command right, and everything downstream (your inserts, your queries, your application logic) becomes easier. Get it wrong, and you’ll spend hours down the line fighting data that doesn’t behave the way you expected. I want to walk you through CREATE TABLE in SQLite thoroughly, because the small decisions you make here tend to echo through the entire lifetime of a project.
The Basic Syntax
Here’s the simplest possible table definition:
CREATE TABLE students (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER
);
This creates a table called students with three columns: id, name, and age. Each column has a declared type, though as you’ll see later, SQLite treats types a bit differently than most other database systems.
Understanding INTEGER PRIMARY KEY
The id INTEGER PRIMARY KEY line deserves special attention because it behaves differently in SQLite compared to most databases. When a column is declared exactly as INTEGER PRIMARY KEY, SQLite treats it as an alias for the table’s built-in row ID. This means it automatically increments with each new row, you don’t need to specify a value when inserting, and it’s guaranteed to be unique.
INSERT INTO students (name, age) VALUES ('Zara', 21);
INSERT INTO students (name, age) VALUES ('Ahmed', 23);
SELECT * FROM students;
Both rows get an id assigned automatically — 1 and 2, respectively — even though we never mentioned id in the INSERT statement.
If you specifically want values to keep increasing and never be reused, even after rows are deleted, add AUTOINCREMENT:
CREATE TABLE orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
item TEXT,
quantity INTEGER
);
Without AUTOINCREMENT, if you delete the last row in a table, SQLite might reuse that same ID for the next inserted row. With AUTOINCREMENT, that never happens — IDs always increase, permanently. Most projects don’t strictly need this, and it does come with a small performance cost, but it matters when you’re dealing with data where ID reuse could genuinely cause confusion, like order numbers or ticket IDs referenced externally.
SQLite’s Data Types
SQLite uses a system called type affinity rather than strict static typing like you’d find in most other relational databases. There are five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB. When you declare a column type in CREATE TABLE, you’re really telling SQLite which storage class that column prefers, not enforcing an absolute rule.
CREATE TABLE inventory (
id INTEGER PRIMARY KEY,
product_name TEXT,
quantity INTEGER,
unit_price REAL,
photo BLOB
);
TEXT is for strings, INTEGER for whole numbers, REAL for floating-point numbers, and BLOB for binary data like images or files. There’s no dedicated BOOLEAN type — SQLite typically represents true/false using INTEGER, with 0 and 1.
You can even use type names SQLite doesn’t formally recognize, like VARCHAR(255) or DATETIME, and it will still work, quietly mapping them to the closest storage class affinity. This flexibility is convenient for beginners but can also mask mistakes, so it’s worth understanding rather than just relying on blindly.
Constraints: Keeping Your Data Honest
A table definition isn’t just about naming columns and types — it’s also where you enforce rules about what data is acceptable. Let’s build out a more realistic table with several constraints:
CREATE TABLE users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL UNIQUE,
age INTEGER CHECK (age >= 13),
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
NOT NULL means this column can never be left empty. Try to insert a row without a username, and SQLite rejects it outright.
UNIQUE means no two rows can share the same value in that column. Two users can’t register the same username or email.
CHECK lets you define a custom validation rule. Here, nobody under 13 can be registered — SQLite enforces that at the database level, regardless of what your application code does or doesn’t check.
DEFAULT provides a fallback value when one isn’t supplied during INSERT. Here, created_at automatically fills in with the current timestamp if you don’t specify one yourself.
INSERT INTO users (username, email, age) VALUES ('zara_k', 'zara@example.com', 22);
SELECT * FROM users;
Notice created_at gets filled in automatically, even though we never mentioned it.
Foreign Keys: Connecting Tables Together
Real applications almost always involve multiple related tables. Foreign keys are how you formally declare those relationships:
CREATE TABLE departments (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department_id INTEGER,
FOREIGN KEY (department_id) REFERENCES departments (id)
);
This tells SQLite that every value in employees.department_id should correspond to an actual id in the departments table. Important caveat: SQLite does not enforce foreign key constraints by default. You need to explicitly turn this on, usually right after opening a connection:
PRAGMA foreign_keys = ON;
Once enabled, trying to insert an employee with a department_id that doesn’t exist in departments will fail with a constraint error, which is exactly the kind of protection you want in a real application.
You can also define what happens when a referenced row gets deleted, using ON DELETE:
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department_id INTEGER,
FOREIGN KEY (department_id) REFERENCES departments (id) ON DELETE CASCADE
);
With ON DELETE CASCADE, deleting a department automatically deletes every employee tied to it. That’s powerful, but also dangerous if used carelessly — think through whether cascading deletes are really what you want before adding this.
CREATE TABLE IF NOT EXISTS
Just like DROP TABLE has an IF EXISTS variant, CREATE TABLE has IF NOT EXISTS, which prevents an error if the table is already there:
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL
);
If users already exists, this statement does nothing and doesn’t throw an error. This is invaluable in setup scripts you might run more than once, since without it, running the same CREATE TABLE statement twice would fail the second time.
Composite Primary Keys
Not every table needs a single-column primary key. Sometimes uniqueness only makes sense across a combination of columns. Consider a table tracking which students are enrolled in which courses:
CREATE TABLE enrollments (
student_id INTEGER,
course_id INTEGER,
enrolled_on TEXT,
PRIMARY KEY (student_id, course_id)
);
Here, the combination of student_id and course_id must be unique — a given student can’t enroll in the same course twice — but either column alone can repeat many times across different rows.
Creating a Table from a Query
Sometimes you want to build a new table based on the shape of an existing one, possibly with a filtered subset of data. CREATE TABLE AS SELECT (often abbreviated CTAS) does exactly that:
CREATE TABLE senior_employees AS
SELECT * FROM employees WHERE department_id = 1;
This creates a brand-new table, senior_employees, with a schema inferred from the SELECT statement’s output, and immediately populates it with matching rows. One thing to be aware of: this approach doesn’t preserve constraints, primary keys, or foreign key relationships from the original table — it only copies the column names, inferred types, and data. If you need those constraints, you’ll need to add them manually afterward or define the table with a full CREATE TABLE statement and use INSERT INTO … SELECT instead.
Temporary Tables
If you need a table that only exists for the duration of your current database connection — useful for intermediate calculations you don’t want cluttering up your schema permanently — use TEMP or TEMPORARY:
CREATE TEMP TABLE scratch_calculations (
id INTEGER PRIMARY KEY,
value REAL
);
Once the connection closes, this table and all its data disappear automatically. I use temporary tables often when building complex reports that need several intermediate steps before producing a final result.
Common Mistakes to Avoid
Forgetting to declare a primary key at all. Every table doesn’t strictly require one, but without it, you lose the convenient auto-incrementing ID behavior and it becomes harder to reference specific rows reliably from other tables.
Assuming type declarations are strictly enforced. Because of type affinity, SQLite is more permissive than most databases about what you can actually insert into a typed column. Don’t rely on the column type alone to catch bad data — use CHECK constraints for real validation.
Not turning on foreign key enforcement. It’s easy to define FOREIGN KEY relationships and assume they’re being enforced, when by default in SQLite, they aren’t unless you explicitly run PRAGMA foreign_keys = ON.
Overusing CREATE TABLE AS SELECT for tables that need real constraints. It’s a convenient shortcut for quick, one-off tables, but it silently drops the NOT NULL, UNIQUE, and FOREIGN KEY rules that a properly designed table usually needs.
Not using IF NOT EXISTS in scripts that might run more than once. This is a small thing that saves a lot of frustration during development.
Best Practices Worth Adopting
Define an explicit primary key on nearly every table, even if it’s just a simple auto-incrementing id.
Use NOT NULL, UNIQUE, and CHECK constraints deliberately, rather than relying entirely on your application code to validate data before it reaches the database.
Turn on foreign key enforcement at the start of every connection if your schema involves relationships between tables.
Use CREATE TABLE IF NOT EXISTS in any setup or initialization script that might reasonably be run more than once.
Think carefully about ON DELETE behavior for every foreign key relationship — decide deliberately between CASCADE, SET NULL, RESTRICT, or leaving it as the default, rather than leaving it unspecified without thinking it through.
Wrapping Up
CREATE TABLE is where your database’s structure gets decided, and structural decisions made early tend to be expensive to change later, especially once real data and real application logic depend on them. Take the time to think through your columns, your types, your constraints, and your relationships before you start inserting data. A well-designed schema, built with the right constraints from the start, will save you far more time than it costs, especially as your application grows and more people start relying on that data being trustworthy.