---
title: "Third normal form (3NF)"
description: "Third normal form (3NF) is a database design rule requiring a table to already be in second normal form and to have no transitive dependencies — every non-key column must depend only on the primary key."
canonical: "https://motherduck.com/glossary/third-normal-form/"
related:
  - title: "Constraints | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/constraints/"
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "No SQL standard for 7 years ?! | MotherDuck"
    url: "https://motherduck.com/videos/no-sql-standard-for-7-years/"
gated_asset:
  title: "DuckLake: The Lakehouse Table Format"
  url: "https://motherduck.com/lp/ducklake-lakehouse-table-format-book-full/"
---

# Third normal form (3NF)

> Third normal form (3NF) is a database design rule requiring a table to already be in second normal form and to have no transitive dependencies — every non-key column must depend only on the primary key.

## Overview

Third normal form is the third step in Codd's sequence of normal forms, and the level most operational database schemas target in practice. A table is in 3NF when:

1. It is already in second normal form (1NF, plus every non-key column depends on the *entire* primary key, not just part of a composite key).
2. It has no **transitive dependencies** — no non-key column depends on another non-key column instead of depending directly on the key.

The classic example: an `orders` table with columns `order_id`, `customer_id`, `customer_city`, and `customer_state`. Here `customer_city` and `customer_state` don't depend on `order_id` (the key) — they depend on `customer_id`, a non-key column. That's a transitive dependency, and it violates 3NF, because updating a customer's city means hunting down and fixing every order row for that customer.

### Example: fixing a 3NF violation

```sql
-- Violates 3NF: customer_city depends on customer_id, not order_id
CREATE TABLE orders_bad (
    order_id      INTEGER PRIMARY KEY,
    customer_id   INTEGER,
    customer_city VARCHAR,
    amount        DECIMAL(10,2)
);

-- 3NF: split out the transitively dependent columns
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    city        VARCHAR
);

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    amount      DECIMAL(10,2)
);
```

After the split, `customer_id`'s city is stored exactly once. Update it in `customers` and every order automatically reflects the correct value — no anomaly possible.

### Why it matters

3NF removes the update, insert, and delete anomalies that plague denormalized transactional schemas: you can't have inconsistent duplicates of a fact, and you can't lose data about an entity just because its last related row was deleted. It's the design target for most OLTP systems.

### DuckDB angle

DuckDB enforces the standard relational constraints (`PRIMARY KEY`, `FOREIGN KEY`) needed to build and query a 3NF schema, but as with normalization generally, 3NF is a source-system and OLTP concern. Warehouses built on DuckDB or MotherDuck typically ingest 3NF data and deliberately denormalize it back into star schemas for analytical speed.