Add a foreign key to associate a queue job with an owner record

By Batu · English technical notes

Also published in our Blogger archive.

Enable foreign keys for this SQLite connection

REFERENCES owner(id) describes the relation, while PRAGMA foreign_keys = ON enables its enforcement on this connection. The missing owner is rejected; after owner 1 exists, the linked job round-trips correctly. A schema declaration alone does not enable enforcement on separately opened connections.

The fixture leaves deletion behavior at SQLite defaults. Cascade, restriction, or reassignment need their own explicit schema rule.

Example

import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    connection = sqlite3.connect(Path(directory) / "queue.db")
    connection.execute("PRAGMA foreign_keys = ON")
    connection.execute("CREATE TABLE owner(id INTEGER PRIMARY KEY)")
    connection.execute("CREATE TABLE job(owner_id INTEGER REFERENCES owner(id))")
    try:
        connection.execute("INSERT INTO job VALUES (99)")
    except sqlite3.IntegrityError:
        rejected = True
    else:
        rejected = False
    assert rejected
    connection.execute("INSERT INTO owner VALUES (1)")
    connection.execute("INSERT INTO job VALUES (1)")
    assert connection.execute("SELECT owner_id FROM job").fetchone()[0] == 1
    print("missing-owner=rejected linked-owner=1")

Expected stdout:

missing-owner=rejected linked-owner=1

Sources

- SQLite Foreign Key Support

- sqlite3.Connection.execute

Prepared with AI assistance. The example uses synthetic data; its stated limits apply.