SQL Server › Normalization

What is the second normal form, and how does it build on the requirements of the first normal form?

Intermediate

Answer

The second normal form requires a table to already be in first normal form, and additionally requires that every non key column depends on the entire primary key, not just part of it, which specifically matters for tables with a composite primary key made up of more than one column.

-- Violates second normal form: ProductName only depends on ProductId, not OrderId
CREATE TABLE OrderItems (OrderId INT, ProductId INT, ProductName VARCHAR(100), Quantity INT);

-- Fixed: ProductName moved to its own table
CREATE TABLE Products (ProductId INT PRIMARY KEY, ProductName VARCHAR(100));
CREATE TABLE OrderItems (OrderId INT, ProductId INT, Quantity INT);

Real-world example An order items table originally stored the product name directly alongside the composite order and product id key, but was redesigned to move the product name into its own dedicated products table, properly satisfying the second normal form.

Common follow-ups: Why does the second normal form only apply to tables with a composite primary key?;What problems occur if a table violates second normal form?

Constraints (Primary Key Foreign Key Check & Unique);Data Types & Schema Design