Create a table

You created the notes table when you made the database file. Now add a table for tags:

CREATE TABLE tags (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
) STRICT;

The schema gives each tag an identifier, requires a name, and prevents duplicate tag names.

Let’s unpack each decision, because every table you create in SQLite repeats this pattern.

id INTEGER PRIMARY KEY gives every row a unique numeric identifier that SQLite assigns for you. The next lesson explains why this exact spelling is special in SQLite.

name TEXT NOT NULL UNIQUE stacks two rules on the column. NOT NULL rejects a missing name; UNIQUE rejects a duplicate one. Rules that must always hold belong in the schema, where SQLite enforces them for every writer.

STRICT at the end tells SQLite to enforce the declared types. Without it, SQLite’s flexible typing lets a well-meaning script store text in an integer column. Strict tables accept only INT, INTEGER, REAL, TEXT, BLOB, and ANY as column types, and they reject values that don’t match. It needs SQLite 3.37 or newer.

Verify the table exists

.tables now lists both tables, and .schema tags prints the definition back:

sqlite> .tables
notes  tags
sqlite> .schema tags
CREATE TABLE tags (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
) STRICT;

What .schema prints is not a reconstruction. SQLite stores the literal text of your CREATE TABLE statement and gives it back verbatim.

Watch out for silent type names

If you bring habits from MySQL or PostgreSQL, you might write VARCHAR(20) instead of TEXT. On an ordinary table SQLite accepts it, quietly maps it to text affinity, and ignores the 20 — a 500-character name inserts without complaint. No error, just an unenforced limit.

On a STRICT table the same column fails immediately:

sqlite> CREATE TABLE t (name VARCHAR(20)) STRICT;
Parse error: unknown datatype for t.name: "VARCHAR(20)"

That early error is a feature. My advice is to declare every table STRICT, use TEXT for strings, and enforce lengths with a CHECK constraint when a limit genuinely matters. A later lesson on storage classes covers what flexible typing does in detail.