Second Normal Form (2NF)
Killing Partial Dependencies
LinkedIn Hook
"You updated a product name in one row and it stayed wrong in 4,000 others. Your table was not in 2NF."
Most developers learn 1NF — atomic columns, no repeating groups — and assume normalization is done. Then they design an
order_itemstable with a composite primary key(order_id, product_id)and casually dropproduct_name,product_price, andproduct_categorynext to it. The query writer is happy. The data is duplicated across every line item. And the first time marketing renames a product, half the order history says "Wireless Mouse" and the other half says "Logitech Wireless Mouse v2."That is a partial dependency. The composite key has two parts, but
product_nameonly depends on one of them. It travels withproduct_idno matter which order it appears on. Storing it on every order line is the database equivalent of writing your phone number on every page of every contract you sign — easy to read, impossible to update.Second Normal Form fixes exactly this: every non-key column must depend on the whole primary key, not just part of it. If your table has a single-column primary key, 2NF is automatic — there is no "part" of the key to depend on. The trap only opens when you have a composite key, which is why 2NF is the most-skipped normal form in textbooks and the most common failure in real schemas.
In Lesson 10.3, I break down 2NF: what a partial dependency is, why it can only happen with composite keys, how to spot one in EXPLAIN-able PostgreSQL examples, and the mechanical split that turns a violating table into a compliant one.
Read the full lesson -> [link]
#SQL #Database #PostgreSQL #Normalization #DataModeling #BackendDevelopment #InterviewPrep
What You'll Learn
- What a partial dependency is and why it only exists when the primary key is composite
- Why a table with a single-column primary key is automatically in 2NF
- How to spot a 2NF violation by reading the columns alongside a composite key
- The mechanical split: extract the partially-dependent attributes into a new table keyed by the part they actually depend on
- A runnable PostgreSQL before/after for an
order_itemsschema and acourse_enrollmentsschema - The update, insert, and delete anomalies that 2NF violations create
- Why 2NF is the gateway to 3NF and BCNF, and why textbooks treat it as the "easy" middle step
- How to defend a 2NF design in an interview without parroting the formal definition
The Library Loan Card Analogy — One Fact, One Place
Imagine an old library loan card. Each row on the card records a single borrowing event: the book ID, the borrower ID, the borrow date, the due date, the borrower's full name, the borrower's home address, the book title, and the book's publisher. The card's natural primary key is (book_id, borrower_id, borrow_date) — that combination identifies a unique loan event. Every other field hangs off that compound key.
But look closer. The borrower's name and address do not depend on which book they borrowed. They depend only on borrower_id. The book title and publisher do not depend on who borrowed it. They depend only on book_id. Only borrow_date and due_date actually depend on the whole loan event. The card is mixing three different kinds of facts — loan facts, borrower facts, and book facts — into one row, and the price is paid every time a borrower moves house. Now every loan card they ever appeared on has a stale address. Update one card, and the rest still lie.
Second Normal Form is the rule that says: each row in a table should describe exactly one kind of thing, and every non-key column on the row must depend on the entire identifying key — not just a piece of it. The fix is the same as splitting the loan card into three: a borrowers ledger keyed by borrower, a books ledger keyed by book, and a loans ledger that just records the event and points at the other two. One fact, one place, one update.
+---------------------------------------------------------------+
| THE LOAN CARD BEFORE 2NF |
+---------------------------------------------------------------+
| |
| PK = (book_id, borrower_id, borrow_date) |
| |
| book_id | borrower_id | borrow_date | due_date | borrower_ |
| | | | | name |
| --------+-------------+--------------+----------+----------- |
| B-101 | U-7 | 2026-01-04 | 26-01-18 | Ana Reyes |
| B-220 | U-7 | 2026-02-11 | 26-02-25 | Ana Reyes |
| B-101 | U-9 | 2026-01-09 | 26-01-23 | Bo Tran |
| |
| Problems: |
| - 'borrower_name' depends only on borrower_id (PART of key) |
| - 'book_title' depends only on book_id (PART of key) |
| - Update Ana's name -> must touch every loan row she has |
| - Cannot record a new borrower until they borrow a book |
| - Delete the last loan of a book -> book metadata vanishes |
| |
+---------------------------------------------------------------+
|
| apply 2NF
v
+---------------------------------------------------------------+
| THE LOAN CARD AFTER 2NF |
+---------------------------------------------------------------+
| |
| borrowers (PK = borrower_id) |
| borrower_id | borrower_name | borrower_address |
| ------------+---------------+----------------- |
| U-7 | Ana Reyes | 14 Pine St |
| U-9 | Bo Tran | 88 Oak Ave |
| |
| books (PK = book_id) |
| book_id | book_title | publisher |
| --------+----------------------+----------- |
| B-101 | The Pragmatic ... | Addison-Wesley |
| B-220 | Designing Data ... | O'Reilly |
| |
| loans (PK = (book_id, borrower_id, borrow_date)) |
| book_id | borrower_id | borrow_date | due_date |
| --------+-------------+-------------+---------- |
| B-101 | U-7 | 2026-01-04 | 26-01-18 |
| B-220 | U-7 | 2026-02-11 | 26-02-25 |
| B-101 | U-9 | 2026-01-09 | 26-01-23 |
| |
| Now: one fact -> one place -> one update |
| |
+---------------------------------------------------------------+Napkin AI Visual Prompt: "Dark gradient (#0a0f1f -> #111a2e). Split layout: LEFT side labeled 'Before 2NF' shows a single wide table with a composite key highlighted sky blue (#4fc3f7) and rose-colored (#ff5c8a) arrows leaving individual key columns to non-key columns, captioned 'partial dependency'. RIGHT side labeled 'After 2NF' shows three smaller tables connected by sky blue lines: 'borrowers', 'books', and a thin 'loans' join table in the center. White monospace labels. A rose 'one fact, one place' sticker between the two sides."
The Formal Definition — In Plain English
A relation is in Second Normal Form if and only if:
- It is already in First Normal Form (atomic columns, no repeating groups, a defined primary key).
- Every non-prime attribute is fully functionally dependent on the entire primary key — not on any proper subset of it.
A "non-prime attribute" is just any column that is not part of the primary key. A "partial dependency" is when a non-prime column can be determined by looking at only part of a composite key. The cure is to extract those columns into their own table, keyed by exactly the part they depend on.
The most important corollary: if your primary key is a single column, your table is automatically in 2NF. There is no "part" of a one-column key, so the rule is vacuously satisfied. This is why 2NF is the rarest violation in modern schemas that use surrogate id BIGSERIAL PRIMARY KEY columns everywhere — and why it bites the hardest when it does occur, in tables that legitimately use composite natural keys.
+---------------------------------------------------------------+
| WHEN CAN 2NF EVEN BE VIOLATED? |
+---------------------------------------------------------------+
| |
| PK = (id) -> always 2NF |
| PK = (uuid) -> always 2NF |
| PK = (order_id, line_no) -> 2NF AT RISK |
| PK = (student_id, course_id) -> 2NF AT RISK |
| PK = (book_id, borrower_id, date) -> 2NF AT RISK |
| |
| Rule of thumb: |
| No composite key -> no partial dependency possible |
| Composite key -> audit every non-key column |
| |
+---------------------------------------------------------------+Example 1 — Order Items, the Classic 2NF Trap
The textbook 2NF violation lives in e-commerce. An order_items table uses the composite key (order_id, product_id) because each order can contain each product at most once. The temptation is to drop product attributes right next to the key for "convenience."
The Violating Schema
-- DROP first so the script is rerunnable end-to-end
DROP TABLE IF EXISTS order_items_bad;
CREATE TABLE order_items_bad (
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(10,2) NOT NULL, -- price ON this order, OK
-- The 2NF violators -- these depend only on product_id:
product_name TEXT NOT NULL,
product_category TEXT NOT NULL,
product_weight_g INTEGER NOT NULL,
PRIMARY KEY (order_id, product_id)
);
INSERT INTO order_items_bad VALUES
(1001, 55, 2, 19.99, 'Wireless Mouse', 'Peripherals', 95),
(1001, 72, 1, 9.50, 'USB-C Cable 1m', 'Cables', 40),
(1002, 55, 1, 19.99, 'Wireless Mouse', 'Peripherals', 95),
(1003, 55, 3, 17.99, 'Wireless Mouse', 'Peripherals', 95),
(1003, 72, 2, 9.50, 'USB-C Cable 1m', 'Cables', 40);
SELECT order_id, product_id, product_name, product_category
FROM order_items_bad
ORDER BY order_id, product_id;Output:
order_id | product_id | product_name | product_category
----------+------------+-------------------+------------------
1001 | 55 | Wireless Mouse | Peripherals
1001 | 72 | USB-C Cable 1m | Cables
1002 | 55 | Wireless Mouse | Peripherals
1003 | 55 | Wireless Mouse | Peripherals
1003 | 72 | USB-C Cable 1m | CablesNotice that Wireless Mouse / Peripherals / 95g is duplicated three times — once per order line. The composite primary key has two parts (order_id, product_id), but product_name, product_category, and product_weight_g depend on only one part: product_id. That is the textbook partial dependency.
The Anomaly
-- Marketing renames the product. We have to update EVERY order line.
UPDATE order_items_bad
SET product_name = 'Wireless Mouse v2'
WHERE product_id = 55;
-- Forget one row, and history disagrees with itself.
-- Worse: there is no constraint forcing all rows for product_id=55
-- to share the same product_name. The schema permits inconsistency.
UPDATE order_items_bad
SET product_name = 'Wireless Mouse v2 PRO'
WHERE order_id = 1003 AND product_id = 55;
SELECT product_id, product_name, COUNT(*) AS rows
FROM order_items_bad
WHERE product_id = 55
GROUP BY product_id, product_name;Output:
product_id | product_name | rows
------------+-----------------------+------
55 | Wireless Mouse v2 | 2
55 | Wireless Mouse v2 PRO | 1Same product, two names, zero way to tell which one is correct. This is an update anomaly, and it is the practical cost of a 2NF violation.
The 2NF-Compliant Split
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS products;
-- products table: keyed by what product attributes ACTUALLY depend on
CREATE TABLE products (
product_id BIGINT PRIMARY KEY,
product_name TEXT NOT NULL,
product_category TEXT NOT NULL,
product_weight_g INTEGER NOT NULL
);
-- order_items: only attributes that depend on the WHOLE composite key
CREATE TABLE order_items (
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL REFERENCES products(product_id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(10,2) NOT NULL, -- price AT TIME OF ORDER
PRIMARY KEY (order_id, product_id)
);
INSERT INTO products VALUES
(55, 'Wireless Mouse', 'Peripherals', 95),
(72, 'USB-C Cable 1m', 'Cables', 40);
INSERT INTO order_items VALUES
(1001, 55, 2, 19.99),
(1001, 72, 1, 9.50),
(1002, 55, 1, 19.99),
(1003, 55, 3, 17.99),
(1003, 72, 2, 9.50);
-- One UPDATE, one row, every order line stays consistent.
UPDATE products SET product_name = 'Wireless Mouse v2' WHERE product_id = 55;
SELECT oi.order_id, oi.product_id, p.product_name, oi.quantity, oi.unit_price
FROM order_items oi
JOIN products p USING (product_id)
ORDER BY oi.order_id, oi.product_id;Output:
order_id | product_id | product_name | quantity | unit_price
----------+------------+-------------------+----------+------------
1001 | 55 | Wireless Mouse v2 | 2 | 19.99
1001 | 72 | USB-C Cable 1m | 1 | 9.50
1002 | 55 | Wireless Mouse v2 | 1 | 19.99
1003 | 55 | Wireless Mouse v2 | 3 | 17.99
1003 | 72 | USB-C Cable 1m | 2 | 9.50One row updated. Five order lines reflect the change. Inconsistency is now structurally impossible because there is exactly one place where the product name lives.
A subtle point worth defending in interviews: unit_price stays on order_items, even though "price" feels like a product attribute. It is not — unit_price is the price charged on this specific order, which can differ from today's catalog price because of discounts, promotions, or historical changes. It depends on the whole (order_id, product_id) and therefore correctly belongs on the line item. 2NF is not "move everything to lookup tables." It is "move everything that depends on only part of the key."
Example 2 — Course Enrollments
A second classic violator: a course_enrollments table with a composite key (student_id, course_id).
The Violating Schema
DROP TABLE IF EXISTS enrollments_bad;
CREATE TABLE enrollments_bad (
student_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
enrolled_on DATE NOT NULL,
grade TEXT,
-- 2NF violators - depend only on student_id:
student_name TEXT NOT NULL,
student_email TEXT NOT NULL,
-- 2NF violators - depend only on course_id:
course_title TEXT NOT NULL,
course_credits INTEGER NOT NULL,
PRIMARY KEY (student_id, course_id)
);
INSERT INTO enrollments_bad VALUES
(1, 101, '2026-01-15', 'A', 'Ana Reyes', 'ana@u.edu', 'Databases', 4),
(1, 102, '2026-01-15', 'B+', 'Ana Reyes', 'ana@u.edu', 'Algorithms', 3),
(2, 101, '2026-01-16', 'A-', 'Bo Tran', 'bo@u.edu', 'Databases', 4);
-- Anomaly: cannot record a new student until they enroll in a course
-- INSERT INTO enrollments_bad (student_id, student_name, student_email)
-- VALUES (3, 'Cy Lin', 'cy@u.edu'); -- FAILS - course_id is NOT NULLThe insertion anomaly here is the killer one: there is literally no way to add a student to the system until they sign up for at least one course. The student data is held hostage by the enrollment data.
The 2NF-Compliant Split
DROP TABLE IF EXISTS enrollments;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS courses;
CREATE TABLE students (
student_id BIGINT PRIMARY KEY,
student_name TEXT NOT NULL,
student_email TEXT NOT NULL UNIQUE
);
CREATE TABLE courses (
course_id BIGINT PRIMARY KEY,
course_title TEXT NOT NULL,
course_credits INTEGER NOT NULL CHECK (course_credits > 0)
);
CREATE TABLE enrollments (
student_id BIGINT NOT NULL REFERENCES students(student_id),
course_id BIGINT NOT NULL REFERENCES courses(course_id),
enrolled_on DATE NOT NULL,
grade TEXT,
PRIMARY KEY (student_id, course_id)
);
INSERT INTO students VALUES
(1, 'Ana Reyes', 'ana@u.edu'),
(2, 'Bo Tran', 'bo@u.edu'),
(3, 'Cy Lin', 'cy@u.edu'); -- now allowed, no enrollments needed
INSERT INTO courses VALUES
(101, 'Databases', 4),
(102, 'Algorithms', 3);
INSERT INTO enrollments VALUES
(1, 101, '2026-01-15', 'A'),
(1, 102, '2026-01-15', 'B+'),
(2, 101, '2026-01-16', 'A-');
SELECT s.student_name, c.course_title, e.grade
FROM enrollments e
JOIN students s USING (student_id)
JOIN courses c USING (course_id)
ORDER BY s.student_name, c.course_title;Output:
student_name | course_title | grade
--------------+--------------+-------
Ana Reyes | Algorithms | B+
Ana Reyes | Databases | A
Bo Tran | Databases | A-Three independent things — students, courses, and the relationship between them — now live in three independent tables. A student can exist without enrollments. A course can exist without enrollments. An enrollment requires both, and only stores facts that depend on both.
The Three Anomalies 2NF Eliminates
Every 2NF violation manifests as one of three concrete bugs in production code. Knowing the names is interview gold.
+---------------------------------------------------------------+
| THE THREE ANOMALIES |
+---------------------------------------------------------------+
| |
| UPDATE ANOMALY |
| - One logical fact stored in N rows |
| - Update misses some rows -> data disagrees with itself |
| - Example: rename a product, half the orders show old name |
| |
| INSERTION ANOMALY |
| - Cannot record entity A until entity B exists |
| - Example: cannot add a student without enrolling in course |
| |
| DELETION ANOMALY |
| - Deleting the last row of one entity erases another |
| - Example: delete the only enrollment for a course |
| -> course_title is gone forever |
| |
+---------------------------------------------------------------+All three vanish the moment partially-dependent attributes move to a table keyed by exactly what they depend on. That is the entire payoff of 2NF.
Common Mistakes
1. Believing 2NF only matters when you have composite primary keys, and therefore ignoring it.
This is technically true and practically dangerous. Modern schemas lean on surrogate BIGSERIAL/UUID keys for almost every table, which makes 2NF "automatic." But the moment you build a join table — order_items, enrollments, permissions, tags, memberships, cart_items — you are back in composite-key territory and 2NF is in play again. Junction tables are exactly where 2NF violations love to hide.
2. Confusing "depends on the whole key" with "is mentioned in the key."
A column does not become 2NF-compliant just because it is named after a key column. product_name is not OK on order_items simply because product_id is in the primary key. The test is functional dependency: given only product_id, can I determine product_name? If yes, product_name belongs in products, not order_items.
3. Moving columns that legitimately depend on the whole key.
The mirror of mistake 2. unit_price on order_items looks like a product attribute, but it represents the price charged on this order line and can differ from the catalog price. It depends on (order_id, product_id) and stays on the line item. 2NF asks you to move partial dependencies, not every column that "feels like" a lookup.
4. Adding a surrogate key to a junction table to "fix" 2NF without removing the partial dependencies.
Slapping id BIGSERIAL PRIMARY KEY onto order_items_bad and demoting the composite key to a UNIQUE constraint technically makes the table "automatically 2NF" (no composite PK, no partial dependency possible). But the duplicate product_name rows are still there, and all three anomalies still bite. 2NF is about the data shape, not the key shape — surrogate keys do not absolve you from auditing dependencies.
5. Stopping at 2NF and assuming the schema is "normalized." 2NF only outlaws partial dependencies on the primary key. It says nothing about transitive dependencies (a non-key column depending on another non-key column), which is exactly what 3NF tackles. A table can be in 2NF and still have ugly redundancy. Treat 2NF as a step, not a destination — the next lesson on 3NF closes the remaining gap.
Interview Questions
1. "What is Second Normal Form, and when can a table even violate it?"
Second Normal Form requires that the table is in 1NF and that every non-key column is fully functionally dependent on the entire primary key — not on any proper subset of it. A table can only violate 2NF if its primary key is composite (made of more than one column), because only then is there a "subset" of the key for a column to partially depend on. With a single-column primary key, 2NF is automatic. The classic violator is a junction table like order_items with PK (order_id, product_id) that also stores product_name — product_name depends only on product_id, which is part of the key but not the whole key, so the table fails 2NF. The fix is to move product_name into a separate products table keyed by product_id.
2. "Walk me through the anomalies a 2NF violation causes in production."
There are three named anomalies and they all show up in real bug tickets. Update anomaly: the same logical fact (a product name, a customer address) is duplicated across many rows, so updating it requires touching every row, and missing any of them silently corrupts the data. Insertion anomaly: you cannot record a standalone entity because the table forces you to also supply the other half of the composite key — you cannot add a student to the system until they enroll in a course, because course_id is NOT NULL and part of the primary key. Deletion anomaly: deleting the last row referencing one entity erases information about another entity entirely — delete the last enrollment in a course, and the course title disappears with it. All three vanish when partially-dependent attributes are extracted into their own tables keyed by the exact subset they depend on.
3. "If I add a surrogate id BIGSERIAL PRIMARY KEY to a junction table, is it automatically in 2NF?"
Technically yes, practically no. With a single-column surrogate primary key, there is no proper subset of the key for a column to partially depend on, so the formal definition of 2NF is satisfied. But the original problem — duplicated product or student data across rows — is unchanged. The update, insertion, and deletion anomalies still occur. This is why experienced data modelers treat 2NF as a property of the data shape rather than the key shape: you should still audit each non-key column and ask "what does this functionally depend on?" If it depends on something narrower than the natural identifier of the row, extract it. The surrogate key is a convenience, not a normalization shortcut.
4. "Why is 2NF the most-skipped step in normalization tutorials?"
Because in modern schemas almost every table uses a surrogate id primary key, which makes 2NF trivially satisfied without any thought, so it looks like a non-issue. The catch is that as soon as you build a junction table for a many-to-many relationship — order_items, enrollments, tags, roles_users, cart_items — you are back to a composite key and 2NF is in play. Junction tables are also exactly where developers are most tempted to inline "convenient" lookup data like names and titles to avoid joins. So 2NF gets skipped in tutorials because it rarely applies, then it gets violated in real codebases precisely in the spots where it does apply. The interview defense is to call 2NF out specifically when designing junction tables: list the composite key, then check every additional column against it.
5. "Show me a column that looks like a 2NF violation but actually is not."
The canonical example is unit_price on order_items with PK (order_id, product_id). At first glance it looks like a product attribute that should live on products — and indeed there is usually a products.list_price column too. But unit_price on the line item represents the price actually charged on that specific order, which can differ from today's catalog price because of discounts, promotions, currency conversion, or simply historical changes. It depends on the whole composite key — change either the order or the product and the value can change — so it correctly belongs on order_items. The lesson is that 2NF asks about functional dependencies, not column names. Two columns can have similar names and live in different tables for excellent reasons. Always ask "given only part of the key, can I determine this value?" If no, the column belongs where it is.
Quick Reference — Cheat Sheet
+---------------------------------------------------------------+
| 2NF CHEAT SHEET |
+---------------------------------------------------------------+
| |
| DEFINITION: |
| 1. Already in 1NF |
| 2. Every non-key column depends on the WHOLE primary key, |
| not on a proper subset of it |
| |
| WHEN VIOLATIONS ARE POSSIBLE: |
| Only when the primary key is COMPOSITE. |
| Single-column PK -> automatically 2NF. |
| |
| THE TEST: |
| For each non-key column C, ask: |
| "Given only PART of the PK, can I determine C?" |
| If yes -> partial dependency -> extract to a new table |
| keyed by exactly that part. |
| |
| THE SPLIT: |
| 1. Identify the partially-dependent attributes |
| 2. Create a new table keyed by the subset they depend on |
| 3. Add a FK from the original table to the new one |
| 4. Drop the moved columns from the original |
| |
| THE ANOMALIES 2NF KILLS: |
| - UPDATE anomaly (duplicated fact, partial updates lie) |
| - INSERT anomaly (cannot add A without B) |
| - DELETE anomaly (deleting B erases A) |
| |
+---------------------------------------------------------------+
+---------------------------------------------------------------+
| KEY RULES |
+---------------------------------------------------------------+
| |
| 1. 2NF only matters when the PK is composite |
| 2. Junction tables are the #1 hiding place for violations |
| 3. Move partial dependencies, not every "lookup-ish" column |
| 4. unit_price stays on order_items - it depends on the WHOLE |
| 5. Surrogate IDs do not absolve you from dependency auditing |
| 6. 2NF is a step toward 3NF, not the destination |
| 7. "Given part of the PK, can I derive this?" -> the test |
| 8. One fact, one place, one update |
| |
+---------------------------------------------------------------+| Concern | 2NF-Violating | 2NF-Compliant |
|---|---|---|
| Composite PK with lookup data | order_items(order_id, product_id, product_name, ...) | order_items + separate products |
| Storing a customer name | On every order line | On customers, joined by FK |
| Adding a new entity | Requires a relationship row | Insert into the entity table directly |
| Renaming a product | Update N order lines | Update 1 row in products |
| Deleting last enrollment | Course title disappears | Course row untouched |
| Test for compliance | "Looks denormalized" | "Every column depends on the whole PK" |
| Single-column PK table | N/A | Automatically 2NF |
| Junction table | Audit every non-key column | Only attributes of the relationship |
Prev: Lesson 10.2 -- First Normal Form Next: Lesson 10.4 -- Third Normal Form
This is Lesson 10.3 of the Database Interview Prep Course -- 12 chapters, 58 lessons.