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.

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;

The result must be 1. 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;

Use PRAGMA foreign_key_check; to find existing violations. No returned rows means SQLite found none.