Protect data with constraints

Constraints make database rules explicit. A required title, a unique name, or a check on a boolean value belongs in the schema.

The alternative is enforcing rules in application code, and that fails the moment anything else touches the database: a migration script, a second service, you in the shell at midnight. The schema is the one gate every write passes through.

You’ve already used NOT NULL and UNIQUE. Watch them work:

sqlite> INSERT INTO tags (name) VALUES ('planning');
sqlite> INSERT INTO tags (name) VALUES ('planning');
Runtime error: UNIQUE constraint failed: tags.name (19)

The first insert succeeds, the duplicate is rejected, and nothing was written by the failed statement. The error names the table and column, so violations are easy to trace.

Foreign keys are off by default

SQLite foreign-key enforcement is a connection setting. Enable it before starting a transaction:

PRAGMA foreign_keys = ON;

Now read the setting back:

PRAGMA foreign_keys;
-- 1

The result must be 1. This is a per-connection switch kept off for historical compatibility — it does not persist in the database file. Do this check for every connection unless your database library documents that it enables foreign keys for you.

Once enforcement is active, this relationship rejects a note_id that does not exist:

CREATE TABLE note_tags (
  note_id INTEGER NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
  tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (note_id, tag_id)
) STRICT;

Try it. Inserting a link to a note id that was never created fails with Runtime error: FOREIGN KEY constraint failed (19). And ON DELETE CASCADE means deleting a note automatically deletes its rows in note_tags, so no orphaned links accumulate.

Find violations that already exist

Enforcement only guards new writes. Rows inserted while the pragma was off may already break the rules. Use PRAGMA foreign_key_check; to find existing violations. No returned rows means SQLite found none.

Run that check after enabling foreign keys on an existing database and after any bulk import. Discovering orphaned rows during a quiet moment is a much better experience than discovering them when a join silently returns fewer results than expected.

The realistic failure here is forgetting the pragma in the application. Everything appears to work — inserts succeed, queries run — but references are never verified, and months of orphaned rows pile up. Make the PRAGMA foreign_keys check part of every connection setup, right next to opening the database.