Master Database Normalization: 1NF, 2NF, 3NF & Key Constraints Explained

Database Normal Forms

Normalization, called Normal Form, was introduced by E.F. Codd in the 1970s as the theoretical foundation of relational databases. It provides rules for designing database schemas.

First Normal Form (1NF)

Each column must contain indivisible atomic values. Example: a combined address and phone number should be split into separate fields.

Second Normal Form (2NF)

After satisfying 1NF, every non‑key column must depend on the whole primary key; a table should describe only one entity. For instance, an order table should contain only order‑related columns, while product details belong in a separate product table.

Third Normal Form (3NF)

Beyond 2NF, each column must depend directly on the primary key, not transitively. After separating customers into a customer table, the order table keeps only the customer ID, not other customer attributes.

Key points: 2NF vs 3NF differ in whether separate tables have been created; 2NF requires splitting a table that mixes different entities, while 3NF ensures that each table contains only foreign‑key references to other tables. All three forms must be satisfied sequentially.

Five Major Database Constraints

The main constraints are:

Primary Key Constraint : ensures uniqueness and non‑null values; only primary keys can be auto‑increment.

Unique Constraint : guarantees uniqueness, may allow nulls but only one null per column.

Default Constraint : defines a default value for a column.

Foreign Key Constraint : establishes relationships between tables. Only InnoDB supports foreign keys; the referenced and referencing columns must share the same data type, and the foreign key column must be indexed.

Not Null Constraint : prohibits null values in a column.

Foreign key 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, NO ACTION, CASCADE, and SET NULL, determining how changes in the parent table affect the child table.

Source: 吴建锐, http://www.cnblogs.com/wujianrui/p/7077864.html