Second Normal Form (2NF): eliminating partial dependencies

Second Normal Form

A table is in Second Normal Form (2NF) when it is already in 1NF and every non-key column depends on the entire primary key, not just part of it. 2NF violations only appear in tables with composite primary keys.

Detecting the Violation

Consider an order_items table with PK (order_id, product_id):

-- Violates 2NF
CREATE TABLE order_items_bad (
  order_id     INT,
  product_id   INT,
  quantity     INT,
  product_name VARCHAR(100),  -- depends only on product_id
  unit_price   DECIMAL(10,2), -- depends only on product_id
  PRIMARY KEY (order_id, product_id)
);

product_name and unit_price depend only on product_id, not on the full key. If a product's name changes, you update hundreds of order rows.

The Fix

-- Satisfies 2NF
CREATE TABLE order_items (
  order_id   INT,
  product_id INT,
  quantity   INT NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE products (
  product_id   INT PRIMARY KEY AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  unit_price   DECIMAL(10,2) NOT NULL
);

Each non-key column now lives in the table whose PK it fully depends on.