A primary key is the column, or combination of columns, chosen to identify each row in a relational database table. Its intended contract is unique and non-null: one key identifies at most one stored row, and every row has an identity. In a quant order ledger, that stops a repeated order identifier from quietly becoming an additional order.
A table has at most one primary key, but that key may contain several columns. Other candidate identifiers can be protected with separate uniqueness constraints. PostgreSQL enforces both uniqueness and non-nullness for primary-key columns; SQLite has historical exceptions explained below. PostgreSQL's constraint documentation gives the standard shape of the rule.
A duplicate identifier is a conflict
Take a fictional ledger with two orders: order
101 has quantity 2, and order 102 has quantity 3. A second insertion using 101 violates the key even if the new quantity differs. The database does not infer which version is correct.
The primary key protects identity. The attempted quantity of 9 never replaces the stored quantity of 2 in this plain INSERT example.
import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("""
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
quantity INTEGER NOT NULL
)
""")
conn.executemany(
"INSERT INTO orders VALUES (?, ?)", [(101, 2), (102, 3)]
)
conn.commit()
try:
conn.execute("INSERT INTO orders VALUES (?, ?)", (101, 9))
except sqlite3.IntegrityError:
print("Duplicate order_id rejected")
# Duplicate order_id rejected
rows = conn.execute(
"SELECT order_id, quantity FROM orders ORDER BY order_id"
).fetchall()
print(rows)
# [(101, 2), (102, 3)]
print(len(rows))
# 2
conn.close()The initial rows are committed before the deliberately failed statement. This example uses plain
INSERT with SQLite's default conflict handling; an explicitly chosen replacement or upsert policy can behave differently. Parameter placeholders bind values, and ORDER BY makes the displayed row order deliberate. Tested with Python 3.14.4 and SQLite 3.53.0.Choosing what counts as the same row
A key is a modelling decision about the table's grain: what one row represents.
| Key choice | Meaning | Quant example and limitation |
|---|---|---|
| Natural key | An identifier already used by the business | A broker's order reference, if it is stable and unique within the intended scope. |
| Surrogate key | An identifier introduced for storage | An internal order_id allocated to each row. It simplifies references but does not prove the underlying business event is new. |
| Composite key | Several columns identify a row together | (venue, venue_order_id) when each venue has its own order-number namespace. Either column may repeat; the pair must not. |
These labels overlap: a composite key may be a natural key. For the venue example in SQLite, declare both
venue TEXT NOT NULL and venue_order_id TEXT NOT NULL, then declare PRIMARY KEY (venue, venue_order_id). Two venues can both use order reference A7; a second A7 at the same venue conflicts.Similarly,
(instrument_id, date) can identify one daily mark per instrument only if that is the intended grain. If the table retains several vendors or revisions, that pair is too restrictive. Widen the business identity or model the revisions separately. Adding an arbitrary unique row number alone would permit conflicting marks to coexist undetected.A foreign key can use this identity to connect a fill to its order. A database index concerns how data is found; the primary-key constraint expresses which row identities are allowed.
Two boundaries worth keeping clear
Non-null is a schema contract, with SQLite-specific details. Ordinary SQLite tables retain a historical exception that can permit
NULL in text or composite primary keys without explicit NOT NULL. Do not rely on the words PRIMARY KEY alone for those declarations. SQLite's ordinary INTEGER PRIMARY KEY is special: inserting NULL can request an automatically assigned integer identifier, rather than fail. The stored identifier is still an integer. See SQLite's CREATE TABLE rules.A primary key helps with idempotency, but does not implement it. Retrying the insertion with
101 produces a key conflict here, rather than a successful replay of the original response. Retrying the same business order with a fresh surrogate ID may create another row. A reliable retry design needs a stable request or business-event identifier, rules for conflicting payloads, and a transaction boundary covering the relevant effects. The key supplies an integrity check within that design.For a fuller price-table example, SQL and Persistent Data builds a composite instrument-date key and queries a research panel. The public page is a preview; the full lesson requires membership.
Sources
- PostgreSQL 18: primary-key constraints — uniqueness, non-nullness and multi-column keys.
- SQLite: CREATE TABLE — primary keys, historical null behavior and default conflict handling.
- SQLite: ROWID and automatic integer allocation — why an inserted null can generate an integer key.
- Python 3.14: sqlite3 — parameter binding, execution and integrity errors.