SQL Database Normalization: 1NF, 2NF and 3NF With Runnable SQL

SQL database normalization is the process of splitting one wide table into several related tables so that each fact is stored once and a primary key identifies each row. This guide goes through first, second and third normal form (1NF, 2NF, 3NF) with SQL you can run in PostgreSQL, adds the foreign-key rules that keep the split tables consistent, and ends with a JOIN query that proves the split lost nothing. For the engine-neutral idea behind it, see the broader guide to what data normalization means; this page is the SQL-specific walk-through.

What database normalization is used for in research

Microsoft’s description defines normalization as “the process of organizing data in a database”, with the goal of eliminating redundancy and inconsistent dependency. In a research setting the same problem appears whenever one spreadsheet-style table mixes several kinds of record: participants, samples, instruments and measurements in a single row, or repeated columns such as Measure1, Measure2, Measure3. A fact that is stored in many rows has to be corrected in many rows, and the copies drift apart. Normalizing moves each fact to the one table it belongs to and links tables by keys. If your data currently arrives as a flat file, reading a CSV into R or exploring it in Python are common first steps before you design the tables. Documenting the resulting table structure also feeds directly into a data management plan and a metadata schema.

Install or access a SQL engine

The examples below are written for PostgreSQL, and the syntax for primary keys, foreign keys and unique constraints is the one described in the PostgreSQL documentation, section 5.5 “Constraints”. Use the documentation that matches your server version at postgresql.org/docs. If you work in MySQL instead, the foreign-key syntax and rules are in the MySQL 8.4 Reference Manual, section “FOREIGN KEY Constraints”. Run the statements in the order shown, in an empty test database, using psql or any SQL client connected to it.

Step-by-step: normalize a table to 3NF in SQL

The starting table follows the worked example in Microsoft’s database normalization description: each student has an advisor, the advisor’s room, and up to three classes. The values below are taken from that example; the SQL is written for this guide.

Step 0. The unnormalized table

CREATE TABLE advising_raw (
  student_no   integer,
  advisor      text,
  advisor_room integer,
  class1       text,
  class2       text,
  class3       text
);

INSERT INTO advising_raw VALUES
  (1022, 'Jones', 412, '101-07', '143-01', '159-02'),
  (4123, 'Smith', 216, '101-07', '143-01', '179-04');

This table has no key and repeats a group of columns (class1 to class3). Asking “which students take class 143-01” requires checking three columns.

Step 1. First normal form (1NF): remove repeating groups

Microsoft’s description of 1NF is to eliminate repeating groups, create a separate row or table for each set of related data, and identify each set with a primary key. Here that means one class per row, with a composite primary key. PostgreSQL’s documentation shows composite keys as PRIMARY KEY (a, c) and notes that a primary key is unique and not null, and that a table can have at most one.

CREATE TABLE advising_1nf (
  student_no   integer,
  advisor      text,
  advisor_room integer,
  class_no     text,
  PRIMARY KEY (student_no, class_no)
);

INSERT INTO advising_1nf
SELECT student_no, advisor, advisor_room, class1 FROM advising_raw WHERE class1 IS NOT NULL
UNION ALL
SELECT student_no, advisor, advisor_room, class2 FROM advising_raw WHERE class2 IS NOT NULL
UNION ALL
SELECT student_no, advisor, advisor_room, class3 FROM advising_raw WHERE class3 IS NOT NULL;

The table now has six rows, one per student and class. The advisor and room are repeated on every row for the same student, which the next step addresses.

Step 2. Second normal form (2NF): split what depends on only part of the key

Microsoft’s description of 2NF is to create separate tables for values that apply to multiple records and relate them with a foreign key; records should not depend on anything other than the table’s primary key (a compound key, if necessary). The advisor and room depend on student_no alone, not on the pair (student_no, class_no), so they move to a students table. PostgreSQL’s documentation defines a foreign key as a rule that values in a column must match values appearing in some row of another table, and shows the shorthand REFERENCES products (product_no).

CREATE TABLE students (
  student_no   integer PRIMARY KEY,
  advisor      text,
  advisor_room integer
);

CREATE TABLE registration (
  student_no integer REFERENCES students (student_no),
  class_no   text,
  PRIMARY KEY (student_no, class_no)
);

INSERT INTO students
SELECT DISTINCT student_no, advisor, advisor_room FROM advising_1nf;

INSERT INTO registration
SELECT student_no, class_no FROM advising_1nf;

Step 3. Third normal form (3NF): remove fields that do not depend on the key

Microsoft’s description of 3NF is to eliminate fields that do not depend on the key. In students, advisor_room depends on the advisor, not on the student: it is a fact about the advisor. Move it to a faculty table and point students at it with a foreign key.

CREATE TABLE faculty (
  name text PRIMARY KEY,
  room integer
);

INSERT INTO faculty
SELECT DISTINCT advisor, advisor_room FROM students;

ALTER TABLE students ADD FOREIGN KEY (advisor) REFERENCES faculty (name);
ALTER TABLE students DROP COLUMN advisor_room;

Each fact now lives in one place: an advisor’s room in faculty, a student’s advisor in students, and each class registration in registration.

Test a suspected dependency in your own data

Before moving a column, you can test whether one column determines another with a grouped query. This is a general SQL technique shown here as an illustration, not a rule from the sources above. A query that returns no rows means the data currently shows one room per advisor; it does not prove the dependency holds in general, which is a question about the real-world meaning of the columns.

SELECT advisor
FROM advising_1nf
GROUP BY advisor
HAVING COUNT(DISTINCT advisor_room) > 1;

Output and how to read it: prove the split lost nothing

A JOIN across the three final tables should reproduce the 1NF rows exactly.

SELECT s.student_no, s.advisor, f.room AS advisor_room, r.class_no
FROM students s
JOIN faculty f ON f.name = s.advisor
JOIN registration r ON r.student_no = s.student_no
ORDER BY s.student_no, r.class_no;

Expect six rows: 1022 with Jones, room 412, classes 101-07, 143-01 and 159-02; and 4123 with Smith, room 216, classes 101-07, 143-01 and 179-04. To compare mechanically rather than by eye, run the join as one side of an EXCEPT against the 1NF table and confirm that it returns zero rows in both directions:

(SELECT s.student_no, s.advisor, f.room, r.class_no
 FROM students s
 JOIN faculty f ON f.name = s.advisor
 JOIN registration r ON r.student_no = s.student_no)
EXCEPT
(SELECT student_no, advisor, advisor_room, class_no FROM advising_1nf);

Swap the two sides and run it again. Two empty results mean the normalized tables carry the same information as the 1NF table.

Referential actions: what happens when a parent row is deleted

Normalization creates parent and child tables, so you must decide what happens to child rows when a parent row is deleted. Both engines document the same options on the foreign key:

• NO ACTION is the default when ON DELETE or ON UPDATE is not specified. In PostgreSQL the check can be deferred; in MySQL’s InnoDB engine NO ACTION is equivalent to RESTRICT.
• RESTRICT prevents deletion of a referenced row and, in PostgreSQL, cannot be deferred.
• CASCADE deletes the referencing rows too. PostgreSQL’s example is order_id integer REFERENCES orders ON DELETE CASCADE.
• SET NULL sets the referencing columns to NULL, so those columns must not be declared NOT NULL (MySQL manual).
• SET DEFAULT sets the columns to their default value. The MySQL manual notes that the parser recognizes it but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT.

In the example above, the registration foreign key uses the default. Deleting a student who still has registrations is therefore refused until the registrations are removed; adding ON DELETE CASCADE to that foreign key would remove them automatically instead.

Troubleshooting

• The foreign key will not create (MySQL). The MySQL manual requires an index on the foreign key columns (created automatically if missing), the same size and sign for fixed-precision types such as INTEGER and DECIMAL, and the same character set and collation for character columns.
• A delete is refused. A child row still references the parent and the action is NO ACTION or RESTRICT. Delete the child rows first, or choose CASCADE deliberately.
• Duplicates appear in a UNIQUE column. PostgreSQL’s documentation states that by default nulls are not considered equal in a unique constraint, so several rows with NULL can coexist; NULLS NOT DISTINCT changes that.
• Too many small tables. Microsoft’s description cautions that 3NF is not always practical and that many small tables may degrade performance. It calls third normal form the highest level necessary for most applications and says 4NF and 5NF are rarely considered in practical design.

Free alternatives and engine differences

Engine	Foreign-key detail from its official documentation
PostgreSQL	Default action is NO ACTION; RESTRICT cannot be deferred; a foreign key with no column list refers to the referenced table’s primary key.
MySQL 8.4 (InnoDB)	Default action is NO ACTION, equal to RESTRICT in InnoDB; SET DEFAULT is rejected; matching types and an index are required on foreign key columns.

Frequently asked questions

Is third normal form enough?

For most applications, yes. Microsoft’s description calls third normal form the highest level necessary for most applications and says the higher forms are rarely considered in practical design.

What is the difference between database normalization and SQL normalization?

Normalization is a database design technique; SQL is the language you use to create the resulting tables, keys and constraints and to query across them. The phrase “SQL database normalization” usually means applying the design rules with SQL, as in the steps above.

When should you not normalize fully?

When the split makes the design impractical. The same Microsoft description notes that 3NF is not always practical and that many small tables may degrade performance, so the number of tables is a trade-off to weigh against how the data will be queried.

Sources

• PostgreSQL Global Development Group, PostgreSQL documentation, section 5.5 Constraints: https://www.postgresql.org/docs/current/ddl-constraints.html (checked 2026-10-29).
• Oracle, MySQL 8.4 Reference Manual, FOREIGN KEY Constraints: https://dev.mysql.com/doc/refman/8.4/en/create-table-foreign-keys.html (checked 2026-10-29).
• Microsoft Learn, Database normalization description (archived vendor article, dated 2025-05-26; a vendor description, not a standard): https://learn.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description (checked 2026-10-29).