Creating tables

What does CREATE TABLE need?

A name for the table, then a list of columns in brackets. Each column has a name and usually a type, separated by commas, with no comma after the last one. Rules such as NOT NULL or PRIMARY KEY go after the type. The statement ends with a semicolon like any other.

Use lowercase snake_case names (joined_on, not Joined On) so you never have to quote them. Table names are usually plural nouns: members, loans, books. Avoid names that are SQL keywords, such as order or group, because they need quoting everywhere you use them.

The read-only schema tab in PlainFinch shows the CREATE TABLE statements that built this lesson's tables. They are good models to copy from.

CREATE TABLE members (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT,
  joined_on TEXT
);

Which column types does SQLite have?

SQLite stores every value as one of five storage classes: NULL, INTEGER, REAL (a decimal number), TEXT and BLOB (raw bytes). There is no separate date or boolean type. Dates go in TEXT as YYYY-MM-DD, which sorts correctly and works with the date functions. True and false are stored as 1 and 0.

You can write other type names, such as VARCHAR(50), BOOLEAN or DATETIME, and SQLite accepts them. It maps each one to an affinity using simple rules: a name containing INT becomes INTEGER, one containing CHAR, CLOB or TEXT becomes TEXT, and so on. The (50) in VARCHAR(50) is ignored: SQLite will happily store a 500-character string in it.

For money, pick whole pence or cents in an INTEGER column, or REAL if you accept tiny rounding errors. Say which in the column name (price_pence) so nobody has to guess.

• INTEGER: whole numbers, ids, counts, money in pence.
• REAL: measurements and anything with a fractional part.
• TEXT: names, emails, dates as YYYY-MM-DD.
• BLOB: files or bytes, rarely needed in a beginner schema.

Why does SQLite accept the wrong type in a column?

Ordinary SQLite tables use flexible typing. The column type is a preference called affinity, and SQLite converts a value to it when it can do so without losing anything. The text '1992' going into an INTEGER column is stored as the number 1992. The text 'unknown' can't be converted, so it is stored as text, and no error appears.

This surprises people coming from PostgreSQL or MySQL, which reject values of the wrong type. The typeof() function shows what was actually stored, which is a good way to check data you inherited.

INSERT INTO books (title, author, year)
VALUES ('Unfinished Notes', 'Anon', 'unknown');
SELECT title, year, typeof(year) AS stored_as
FROM books;

When should I use a STRICT table?

Add the word STRICT after the closing bracket and SQLite enforces the declared types. Inserting 'unknown' into an INTEGER column then fails with an error. Text that looks like a number, such as '2026', is still converted. STRICT tables arrived in SQLite 3.37 (2021) and allow only the types INT, INTEGER, REAL, TEXT, BLOB and ANY.

Use STRICT for new tables when you want the database to catch type mistakes from app code or imports. Leave it off when you need to read the file with an SQLite version older than 3.37, or when you deliberately store mixed values in one column.

CREATE TABLE fines (
  id INTEGER PRIMARY KEY,
  loan_id INTEGER NOT NULL REFERENCES loans(id),
  amount_pence INTEGER NOT NULL
) STRICT;
INSERT INTO fines (loan_id, amount_pence) VALUES (2, '150');
SELECT amount_pence, typeof(amount_pence) FROM fines;

What is special about INTEGER PRIMARY KEY?

Every ordinary SQLite table has a hidden whole-number column called rowid that identifies each row. A column declared exactly as INTEGER PRIMARY KEY becomes another name for that rowid. Leave it out of an INSERT and SQLite picks the next number, usually one more than the largest id in the table.

The spelling matters. INT PRIMARY KEY or BIGINT PRIMARY KEY creates a normal column with a unique index. It is not filled in automatically, and because of an old SQLite quirk it even allows NULL.

Adding AUTOINCREMENT guarantees that ids from deleted rows are never reused, at a small cost in speed. Most tables don't need it. PostgreSQL uses GENERATED ALWAYS AS IDENTITY (or the older SERIAL) and MySQL uses AUTO_INCREMENT for the same job.

SELECT rowid, id, title
FROM books
LIMIT 3;

How do I avoid errors when a table already exists?

Running CREATE TABLE twice fails with 'table members already exists'. CREATE TABLE IF NOT EXISTS checks first and does nothing if the name is taken. Setup scripts that may run more than once use it.

Be aware of what it does not do. If an older members table exists with different columns, IF NOT EXISTS leaves it alone and your script carries on with the old shape. It checks only the name.

CREATE TABLE IF NOT EXISTS books (
  id INTEGER PRIMARY KEY,
  title TEXT
);
SELECT COUNT(*) AS columns_in_books
FROM pragma_table_info('books');

How do I change or remove a table later?

ALTER TABLE books ADD COLUMN isbn TEXT; adds a column at the end. Existing rows get NULL, or the DEFAULT if you give one. A new NOT NULL column must have a DEFAULT, because the old rows need some value. ALTER TABLE … RENAME TO renames a table, and RENAME COLUMN old TO new renames a column. SQLite 3.35 and later also support DROP COLUMN, with some limits (it can't drop a key column).

Changing a column's type isn't possible with ALTER TABLE in SQLite. The usual way is to create a new table with the right shape, copy the rows across with INSERT … SELECT, drop the old table and rename the new one, all inside a transaction.

DROP TABLE loans; deletes the table and all its rows. There is no recycle bin. DROP TABLE IF EXISTS avoids an error when the table is already gone. Run the example and open Changes to see the new column and the renamed table.

ALTER TABLE books ADD COLUMN isbn TEXT;
ALTER TABLE loans RENAME COLUMN borrower TO borrower_name;
ALTER TABLE loans RENAME TO book_loans;
SELECT name FROM sqlite_master WHERE type = 'table';

How can I see what a table looks like?

SQLite keeps every CREATE statement in a built-in table called sqlite_master (also readable as sqlite_schema). pragma_table_info('books') returns one row per column with its name, type, whether it is NOT NULL, its default and whether it is part of the primary key. Both are ordinary queries you can filter and count.

When an AI tool writes a schema for you, read it with these. Check that ids are INTEGER PRIMARY KEY, that types are ones SQLite understands the way you expect, and that VARCHAR(n) lengths are not standing in for a rule they don't enforce. In the Tables view you can also see each table and its rows after your code runs.

SELECT name, type, "notnull", dflt_value, pk
FROM pragma_table_info('loans');

Field notes

• CREATE TABLE takes a name and a bracketed list of columns, each with a name and a type.
• SQLite stores values as NULL, INTEGER, REAL, TEXT or BLOB, and has no separate date type.
• Ordinary SQLite tables use flexible typing, so a value of the wrong type can still be stored.
• A column written exactly as INTEGER PRIMARY KEY gets its number filled in when you leave it out.

Easy to get wrong

Only the exact type name INTEGER makes the column an alias for rowid. With INT, ids are not filled in and can even be NULL.

A comma after the last column is a syntax error. AI tools and hand edits leave one behind when a column is deleted.

Existing rows would have no value for the new column, so SQLite refuses a NOT NULL column without a DEFAULT.

DATETIME is accepted, but SQLite has no date type and gives it NUMERIC affinity. Stating TEXT and storing YYYY-MM-DD says plainly what is in the column.

Questions people ask

Do I have to give every column a type?

In an ordinary SQLite table, no: a column with no type accepts anything. Give one anyway. It documents what the column holds and every other database requires it.

Can I rearrange columns after creating a table?

Not directly. Column order rarely makes a difference, because SELECT lists the columns in whatever order you name them. If you want a different order, rebuild the table with the new order and copy the rows over.

Is DROP TABLE undoable?

Inside a transaction, yes: ROLLBACK brings the table back. Outside one, the table and its rows are gone, and only a backup can restore them.

What is the difference between sqlite_master and sqlite_schema?

They are the same table. sqlite_schema is the newer name, added in SQLite 3.33. sqlite_master still works everywhere, which is why older code and tools use it.

How big can a TEXT value be?

By default up to about a billion bytes. Lengths in type names such as VARCHAR(50) are ignored. Add a CHECK rule on length(name) if you really need a limit.