---
title: "Referential integrity"
description: "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."
canonical: "https://motherduck.com/glossary/referential-integrity/"
related:
  - title: "Constraints | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/constraints/"
  - title: "DuckLake: The Definitive Guide — Live Author Q&A with Matt Martin & Alex Monahan | MotherDuck"
    url: "https://motherduck.com/videos/ducklake-definitive-guide-oreilly-book/"
  - title: "Database Concepts | MotherDuck Docs"
    url: "https://motherduck.com/docs/concepts/database-concepts/"
gated_asset:
  title: "DuckLake: The Lakehouse Table Format"
  url: "https://motherduck.com/lp/ducklake-lakehouse-table-format-book-full/"
---

# 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:

```sql
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.