Constraints

Constraints in PizzaSQL fall into two camps: the ones that are actually implemented, and the ones that merely parse. This page is deliberately blunt about the difference, because silently assuming a UNIQUE or FOREIGN KEY constraint is enforced will corrupt data.

The enforced constraints

PRIMARY KEY

• A single column can be PRIMARY KEY, or a table-level PRIMARY KEY (col, ...).
• A table-level composite key uses only the first named column as the effective primary key; true composite keys are not supported.
• An INTEGER PRIMARY KEY column becomes an alias for the implicit rowid. Inserting without a value auto-assigns the next rowid; inserting with a value sets the rowid and advances the counter past it.
• A non-integer primary key (e.g. TEXT PRIMARY KEY) is a normal column with a separate hidden rowid counter.
• The primary key drives duplicate detection on INSERT, and therefore INSERT OR REPLACE/OR IGNORE and ON CONFLICT DO NOTHING/DO UPDATE — all of which are keyed on the primary key only.
• Without any declared primary key, the engine assigns a synthetic _rowid_ primary key, so duplicate and NULL values are allowed in user columns.

NOT NULL

• NOT NULL is enforced on INSERT: inserting a row that omits a NOT NULL column with no default is rejected with missing required column.
• It does not enforce on UPDATE to NULL in all paths, and does not validate type.

DEFAULT

• DEFAULT expr is evaluated when the table is created, then stored and applied to inserted rows that omit the column.
• Because it’s evaluated at DDL time, only constant expressions are useful. There is no DEFAULT (expr) re-evaluation per row.

AUTOINCREMENT

• AUTOINCREMENT parses but has no behaviour. It does not prevent rowid reuse, does not maintain a separate sequence, and behaves exactly like a plain INTEGER PRIMARY KEY.
• It is retained in the schema JSON for compatibility but is never read.

The non-enforced constraints

These are accepted by the parser and then silently discarded — the schema does not store them and no code checks them:

Constraint	Parsed?	Stored?	Enforced?
UNIQUE (col)	yes	no	no
UNIQUE column constraint	yes	no	no
CHECK (expr)	yes	no	no
FOREIGN KEY ... REFERENCES ...	yes	no	no
CREATE UNIQUE INDEX	yes	definition stored	no

Consequences to internalize:

• Duplicate values are allowed in any column except the primary key. If you need uniqueness, enforce it in your application.
• Foreign-key relationships are not validated; deleting a parent row does not cascade, restrict, or set null on children.
• CHECK constraints never run. CREATE TABLE t (age INTEGER CHECK (age > 0)) happily accepts -5.
• CREATE UNIQUE INDEX records an index marked unique in the catalog (it shows up in pg_indexes), but the uniqueness flag is cosmetic — the index still only accelerates lookups and does not reject duplicates.

Why this matters for migrations

SQLite-flavoured schemas often lean on UNIQUE and FOREIGN KEY for integrity. When importing such a schema (SQL dump or SQLite file), those constraints are dropped silently rather than erroring. Inspect the imported schema afterwards and add application-level checks for anything that must stay unique or referentially consistent.

See Compatibility for the wider gap list, and Indexes for how CREATE INDEX/UNIQUE INDEX actually behave at the storage layer.