Referential integrity
Referential integrity is the guarantee that a reference from one table to another — typically a foreign key — always points to a row that actually exists, preventing orphaned records.
Overview
Relational schemas frequently split data across multiple tables and link them with foreign keys — for example, an orders table referencing a customer_id that exists in a customers table. Referential integrity is the constraint that guarantees these references stay valid: you can't insert an order for a customer that doesn't exist, and (depending on configuration) you can't delete a customer while orders still reference them.
Foreign Keys and Enforcement Actions
A FOREIGN KEY constraint is how referential integrity is declared and enforced:
Copy code
CREATE TABLE customers (id INTEGER PRIMARY KEY, name VARCHAR);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id)
);
Many databases let you configure what happens when a referenced row is deleted or updated — ON DELETE CASCADE to delete dependent rows automatically, ON DELETE RESTRICT to block the delete, or ON DELETE SET NULL to null out the reference.
DuckDB and Referential Integrity
DuckDB supports FOREIGN KEY constraints and enforces them on insert: a row can't reference a value that doesn't exist in the referenced table's primary key or unique column. Two important gaps to know about: DuckDB currently parses ON DELETE CASCADE / ON UPDATE CASCADE syntax but does not actually perform the cascading action — deleting a still-referenced parent row raises a constraint error instead — and self-referencing foreign keys aren't supported for inserts. For large bulk loads, foreign key checks add overhead per row, so it's common to validate referential integrity before loading or to load in dependency order (parents before children) rather than relying purely on constraint enforcement during a large batch insert.
Related terms
A foreign key links one table to another by referencing its primary key. Learn how foreign keys enforce referential integrity, with SQL syntax, composite key examples, and data warehousing use cases.
DELETE statement →The DELETE statement removes rows from a table that match an optional WHERE condition, without dropping the table itself.
primary key →A primary key uniquely identifies each database row. Learn SQL definitions, examples, UUID vs auto-increment best practices, and how to fix constraint errors.
UNIQUE constraint →A UNIQUE constraint ensures that all values in a column, or combination of columns, are distinct across every row in a table.
CHECK constraint →A CHECK constraint is a rule attached to a table column (or the table as a whole) that requires every row to satisfy a given boolean expression.
UPSERT →UPSERT is a database operation that inserts a new row or updates an existing one if a matching row already exists, typically expressed with an ON CONFLICT clause.
FAQS
DuckDB accepts the ON DELETE CASCADE syntax but doesn't actually cascade the delete; deleting a referenced row that still has dependents raises a constraint error instead.
Referential integrity is the property being guaranteed — that references always point to existing rows. A FOREIGN KEY constraint is the mechanism a database uses to enforce that guarantee.
