Second Normal Form (2NF)

Second Normal Form (2NF) builds directly on First Normal Form. A table is in 2NF when it is already in 1NF (atomic columns, no repeating groups) and, in addition, every non-key column depends on the entire primary key — not just part of it. This rule only has teeth when the primary key is composite (made of more than one column); if a table's primary key is a single column, it is automatically in 2NF as long as it is already in 1NF.

What a "partial dependency" is

A partial dependency happens when a non-key column's value can be determined by only part of a composite primary key, rather than needing all of it. That is a warning sign that the column is describing something other than what the current table's rows represent, and it belongs elsewhere.

A table that violates 2NF

Consider an order_items table that records which products appear on which orders, along with the quantity ordered. Because a product can appear on many orders and an order can contain many products, the natural primary key is the pair (order_id, product_id):

Violates 2NF: product_name only depends on product_id

SQL

CREATE TABLE order_items (
  order_id     INT,
  product_id   INT,
  product_name VARCHAR(200),
  quantity     INT,
  PRIMARY KEY (order_id, product_id)
);

INSERT INTO order_items (order_id, product_id, product_name, quantity) VALUES
  (1001, 50, 'Wireless Mouse',  2),
  (1001, 51, 'Mechanical Keyboard', 1),
  (1002, 50, 'Wireless Mouse',  1),
  (1003, 52, 'USB-C Cable',     3);

quantity genuinely depends on the whole key: it takes both which order and which product to know how many units were bought. But product_name only depends on product_id — "Wireless Mouse" is the name of product 50 regardless of which order it appears on. That is a partial dependency, and it is already visible in the data above: "Wireless Mouse" is stored twice, once for order 1001 and once for order 1002. If the product were ever renamed, every single order row containing it would need to be updated, and missing even one row would leave the table internally inconsistent — two rows disagreeing about the name of the same product.

Fixing it: moving the partial dependency to its own table

The fix is to pull product_name out into a products table, keyed by product_id alone, and leave order_items holding only the columns that genuinely depend on the full composite key.

2NF-compliant: product_name lives with product_id

SQL

CREATE TABLE products (
  product_id   INT PRIMARY KEY,
  product_name VARCHAR(200) NOT NULL
);

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)
);

INSERT INTO products (product_id, product_name) VALUES
  (50, 'Wireless Mouse'),
  (51, 'Mechanical Keyboard'),
  (52, 'USB-C Cable');

INSERT INTO order_items (order_id, product_id, quantity) VALUES
  (1001, 50, 2),
  (1001, 51, 1),
  (1002, 50, 1),
  (1003, 52, 3);

Now a product's name is stored exactly once. Renaming "Wireless Mouse" is a single UPDATE against products, and every order_items row that references product_id 50 automatically reflects the new name whenever it is joined:

Order details with the name joined back in

SQL

SELECT oi.order_id, p.product_name, oi.quantity
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
WHERE oi.order_id = 1001;

Column	Depends on	Where it belongs
quantity	order_id AND product_id together	order_items (full composite key)
product_name	product_id alone	products (partial key only)

2NF only matters with composite keys

If order_items instead had its own single-column surrogate key, such as an auto-incrementing item_id, the partial dependency would not disappear on its own — product_name would still logically depend only on product_id, and the redundancy would still exist. 2NF is about the underlying functional dependency, not about which column happens to be declared PRIMARY KEY, so it is worth checking for partial dependencies even when a table has a surrogate key layered on top of a natural composite relationship.

A table must be in 1NF before 2NF is even meaningful.
A partial dependency exists when a non-key column depends on only part of a composite primary key.
Tables with a single-column primary key are automatically in 2NF once they are in 1NF.
The fix is to move partially-dependent columns into a separate table keyed by the part of the key they actually depend on.