A foreign key is a column or group of columns whose values are constrained to reference an eligible key in a database table, subject to the constraint's null and matching rules. The referencing table is the child; the referenced table is the parent. They can also be the same table. In a quant system, this can prevent a trade from referring to an instrument identifier absent from the instrument master. This relationship is called referential integrity. PostgreSQL's foreign-key documentation explains the constraint.
An instrument must exist before a trade can reference it
Suppose a fictional relational database has an
instruments table containing ABC. A trades.instrument_id foreign key references instruments.instrument_id:- Trade
1, instrumentABC, quantity2: the instrument exists, so the reference passes. - Trade
2, instrumentUNKNOWN, quantity2: no such instrument exists, so an enforced, immediate constraint rejects the insert.
With enforcement enabled, the reference to ABC succeeds and the reference to UNKNOWN fails. Only trade 1 is stored.
The parent's primary key identifies an instrument. The foreign key checks the trade's reference to it. Many trades may reference the same instrument; a foreign key does not make the child column unique. It also does not fetch instrument attributes: use a database join when querying the related data.
SQLite: declaration and enforcement are separate
SQLite applications should explicitly enable foreign-key enforcement on each connection and verify the setting. Setting
PRAGMA foreign_keys inside an active transaction has no effect. The example therefore enables it before any transaction, using Python's explicit autocommit=True mode. See SQLite's pragma documentation and Python's connection API.This example requires Python 3.12 or later; it was executed with Python 3.14.4 and SQLite 3.53.0. The data are fictional.
import sqlite3
conn = sqlite3.connect(":memory:", autocommit=True)
conn.execute("PRAGMA foreign_keys = ON")
assert conn.execute("PRAGMA foreign_keys").fetchone() == (1,)
conn.executescript("""
CREATE TABLE instruments (
instrument_id TEXT PRIMARY KEY NOT NULL
);
CREATE TABLE trades (
trade_id INTEGER PRIMARY KEY,
instrument_id TEXT NOT NULL REFERENCES instruments(instrument_id),
quantity INTEGER NOT NULL
);
""")
conn.execute("INSERT INTO instruments VALUES (?)", ("ABC",))
conn.execute("INSERT INTO trades VALUES (?, ?, ?)", (1, "ABC", 2))
try:
conn.execute("INSERT INTO trades VALUES (?, ?, ?)", (2, "UNKNOWN", 2))
except sqlite3.IntegrityError:
print("Rejected: no referenced instrument")
# Rejected: no referenced instrument
rows = conn.execute("SELECT * FROM trades ORDER BY trade_id").fetchall()
print(rows)
# [(1, 'ABC', 2)]
conn.close()The
REFERENCES clause declares the relationship. NOT NULL separately makes an instrument reference mandatory: a nullable child key can otherwise bypass a match in SQLite. The default immediate constraint checks this insert when the statement finishes, so the exception is raised there. A deliberately deferred constraint can instead allow a temporary violation within a database transaction, but must be satisfied at commit. SQLite documents these rules.What the constraint does not decide
A foreign key checks the declared relationship, not the economic truth of a trade. Mapping a trade to the wrong existing instrument can still pass. Quantity, currency, price freshness, permissions and whether an instrument was valid on the trade date require separate rules.
The schema design also needs explicit choices:
| Choice | Why it matters |
|---|---|
| Referenced key | In SQLite, use the parent primary key or an eligible unique key; a nonunique identifier is insufficient. |
| Composite identity | If identity uses several columns, reference the full combination in matching order. SQLite permits a composite child key with any null component to avoid the parent match; require each component to be non-null when a reference is mandatory. |
| Parent deletion or key update | This example uses SQLite's default NO ACTION policy and immediate checks: deleting ABC or changing its key would fail while trade 1 references it. CASCADE, SET NULL and other actions have different effects; choose them deliberately. |
These are SQLite foreign-key design rules, rather than promises that every SQL engine has identical null, timing or action semantics. In particular, cascading deletion of an instrument and its historical trades needs a retention policy; existence alone does not justify deleting evidence.
For the preceding schema-design skills, SQL and Persistent Data develops keyed price storage and reproducible queries. Its public preview is open; the full lesson requires membership.
Sources
- PostgreSQL 18: Foreign Keys — referencing and referenced keys, self-references and relationship cardinality.
- SQLite: Foreign Key Support — eligible parent keys, nulls, composite keys, immediate/deferred checks and referential actions.
- SQLite: PRAGMA foreign_keys — connection-level enforcement and transaction restrictions.
- Python: sqlite3 — connection transaction control, placeholders and integrity errors.
- SQLite: CREATE TABLE constraints —
REFERENCES,NOT NULLand primary-key declarations.