Mastering Database Normalization: 1NF, 2NF, 3NF and Key Constraints Explained

Database Normalization Forms

Normalization, or Normal Form, was introduced by E.F. Codd in the 1970s as the theoretical foundation of relational databases. The three basic forms are described below.

First Normal Form (1NF)

Each column must contain atomic, indivisible values. For example, a field "userInfo" containing "Shandong Province Yantai City 1318162008" should be split into "userInfo" = "Shandong Province Yantai City" and "userTel" = "1318162008".

Second Normal Form (2NF)

After satisfying 1NF, every non‑key column must depend on the whole primary key; a table should describe a single entity. Thus an order table should contain only order‑related columns, and a product table only product‑related columns.

Third Normal Form (3NF)

Beyond 2NF, each column must depend directly on the primary key, not transitively. For instance, after extracting a separate customer table, the order table needs only the customer ID, not other customer details.

Key points: 2NF vs 3NF differ in whether separate tables have been created; both 1NF and 2NF must be satisfied before 3NF.

Five Major Database Constraints

The main constraints are:

Primary Key Constraint – ensures uniqueness and non‑null values; can be auto‑increment.

Unique Constraint – guarantees uniqueness, allows a single NULL.

Default Constraint – provides a default value for a column.

Foreign Key Constraint – defines relationships between two tables.

Not Null Constraint – prohibits NULL values.

Foreign Key Details

Only the InnoDB engine supports foreign keys. The foreign key column must have the same data type as the referenced column, and it must be indexed (MySQL creates an index automatically if missing).

Syntax example:

CONSTRAINT fk_name FOREIGN KEY (fk_column) REFERENCES parent_table(parent_column)
ON DELETE SET NULL ON UPDATE CASCADE;

Reference actions include RESTRICT (default), NO ACTION (MySQL only), CASCADE (propagate delete/update), and SET NULL (sets foreign key to NULL, thus the column cannot be NOT NULL).

Primary Key Details

Primary keys are implicitly NOT NULL and unique. Only primary keys can be defined as AUTO_INCREMENT. They can be defined inline: id INT UNSIGNED PRIMARY KEY or after column definitions:

PRIMARY KEY (id)