VisaSpark
Sign in
intermediate22 min

Implementing a Normalized Schema in PostgreSQL

Turning a relational model into real DDL with correctly-ordered CREATE TABLE statements, relationships, constraints, and seed data — and doing it for real, on your own machine.

What you'll learn

  • Determine the correct table-creation order for a schema with foreign key dependencies
  • Write CREATE TABLE statements implementing a normalized design, including constraints
  • Write seed data (INSERT statements) that respects every foreign key relationship

Prerequisites

Explanation

Turning a relational model (the entities, keys, and relationships from this module's earlier lessons) into real DDL has one hard, non-negotiable ordering requirement: a table referenced by a foreign key must exist before the table containing that foreign key is created. CREATE TABLE enrollment (learner_id INTEGER REFERENCES learner(id), ...) fails outright if the learner table doesn't exist yet — PostgreSQL has no way to validate a reference to a table it's never heard of. In practice, this means creating tables in dependency order: entities with no foreign keys first, then tables that reference them, and so on, until junction tables (which typically reference two or more other tables) come last.

The same ordering constraint applies to seed data: an INSERT INTO enrollment (learner_id, course_id) VALUES (1, 10) fails if no learner row with id = 1 exists yet — foreign key constraints are enforced on every insert, seed data included, with no special exemption. Seeding in the wrong order is one of the most common real mistakes when first setting up a normalized schema, and PostgreSQL's error message (naming the specific violated constraint) is usually the fastest way to diagnose exactly which reference came too early.

A complete CREATE TABLE statement combines everything from this module: the right data type per column (previous lesson), PRIMARY KEY (with GENERATED ALWAYS AS IDENTITY for a surrogate key), NOT NULL for required columns, UNIQUE for values like email that must never repeat but aren't the primary key, REFERENCES other_table(column) for foreign keys (with an explicit ON DELETE behavior when the default RESTRICT-like behavior isn't what's wanted), and CHECK (...) constraints for business rules the type system alone can't express (CHECK (price_usd >= 0), CHECK (end_date > start_date)). This lesson's guided local lab has you write and run exactly this — a small, genuinely normalized, multi-table PostgreSQL schema with correctly-ordered creation and seed data, verified against a real PostgreSQL install.

Example

Correctly-ordered CREATE TABLE and INSERT statements for a small normalized schema -- read this, then build and run the real thing yourself in this lesson's guided local lab.

-- learner has no foreign keys -- created first.
CREATE TABLE learner (
    id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

-- course has no foreign keys -- can also be created first, in any order relative to learner.
CREATE TABLE course (
    id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title TEXT NOT NULL,
    price_usd NUMERIC(10,2) NOT NULL CHECK (price_usd >= 0)
);

-- enrollment references BOTH learner and course -- must be created last.
CREATE TABLE enrollment (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    learner_id  INTEGER NOT NULL REFERENCES learner(id) ON DELETE CASCADE,
    course_id   INTEGER NOT NULL REFERENCES course(id) ON DELETE RESTRICT,
    enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (learner_id, course_id) -- a learner cannot enroll in the same course twice
);

-- Seed data must follow the SAME dependency order:
INSERT INTO learner (email) VALUES ('alice@example.com');
INSERT INTO course (title, price_usd) VALUES ('PostgreSQL Fundamentals', 49.00);
INSERT INTO enrollment (learner_id, course_id) VALUES (1, 1); -- fails if either row above doesn't exist yet

Guided exercise

Guided exercise

Write topologicalTableOrder(tables) where tables is an array of {name, dependsOn: [names]} objects. Return an array of table names in a valid creation order (every table appears after everything it depends on). Assume no cycles.

Checks: orders a table with dependencies after all of its dependencies · handles a single table with no dependencies

Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.

Loading editor…

Stuck? Get a hint.

Independent exercise

Independent exercise

Write validateSeedOrder(inserts) where inserts is an array of {table, id, references: [{table, id}]} objects representing INSERT statements in the order they'd run. Return the index of the FIRST insert that references a row not yet inserted by an earlier statement (or -1 if every insert is valid, respecting insertion order).

Checks: validates a correctly-ordered seed sequence · identifies the exact index of a premature, invalid reference · handles an empty insert list

Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.

Loading editor…

Stuck? Get a hint.

Guided local lab

Create and Validate a Normalized PostgreSQL Schema

Runs on your computer
This lab runs on your own computer, in your own terminal and editor — not in your browser. VisaSparkSchools does not execute, run, or verify these commands for you. Follow the verification steps yourself to confirm your result.

Design and build a real, normalized, multi-table PostgreSQL schema for a small learning platform, then seed it with data and verify every constraint actually behaves as designed.

Required tools

  • PostgreSQL server (16 or newer)
  • psql (or another PostgreSQL client) (matching your server version)
  • A terminal (any)

Setup

  1. Install PostgreSQL locally (or use a local install you already have) and confirm it's running: `psql --version` and `psql -U postgres -c "SELECT version();"`.
  2. Create a fresh database for this lab: `createdb learning_platform_lab`.
  3. Create a project folder with a single schema.sql file you'll write and re-run as you iterate.

Project structure

learning-platform-lab/
  schema.sql
  seed.sql

Starter files

schema.sql

-- TODO: CREATE TABLE learner (id IDENTITY PK, email TEXT NOT NULL UNIQUE, display_name TEXT NOT NULL)

-- TODO: CREATE TABLE course (id IDENTITY PK, title TEXT NOT NULL,
--   price_usd NUMERIC(10,2) NOT NULL CHECK (price_usd >= 0))

-- TODO: CREATE TABLE enrollment (id IDENTITY PK,
--   learner_id INTEGER NOT NULL REFERENCES learner(id) ON DELETE CASCADE,
--   course_id INTEGER NOT NULL REFERENCES course(id) ON DELETE RESTRICT,
--   enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
--   UNIQUE (learner_id, course_id))
-- Remember: enrollment must be created AFTER learner and course.

seed.sql

-- TODO: insert at least 2 learners, 2 courses, and 3 enrollments,
-- in an order that respects every foreign key (learner and course rows
-- must exist before any enrollment row referencing them).

Requirements

  • schema.sql creates learner, course, and enrollment tables in valid dependency order.
  • enrollment has a UNIQUE constraint on (learner_id, course_id) preventing a duplicate enrollment.
  • course.price_usd has a CHECK constraint rejecting a negative price.
  • seed.sql successfully inserts at least 2 learners, 2 courses, and 3 enrollments with no foreign key errors.
  • Attempting to insert a duplicate (learner_id, course_id) pair fails with a unique-constraint violation.

Commands to run

  • Apply the schema

    psql -d learning_platform_lab -f schema.sql

  • Apply the seed data

    psql -d learning_platform_lab -f seed.sql

  • Open an interactive session to verify manually

    psql -d learning_platform_lab

Expected behavior

Both schema.sql and seed.sql run with no errors on a fresh database. Querying `SELECT * FROM enrollment;` shows all seeded rows. Attempting to re-run an INSERT that duplicates an existing (learner_id, course_id) pair fails with a unique_violation error, and attempting to insert a course with a negative price fails with a check_violation error.

Verify it yourself

  • psql -d learning_platform_lab -f schema.sql

    Expected: CREATE TABLE printed three times, no errors

  • psql -d learning_platform_lab -f seed.sql

    Expected: INSERT 0 1 (or similar) printed for every insert, no errors

  • psql -d learning_platform_lab -c "INSERT INTO course (title, price_usd) VALUES ('Bad Course', -5.00);"

    Expected: ERROR: new row for relation "course" violates check constraint

  • psql -d learning_platform_lab -c "SELECT count(*) FROM enrollment;"

    Expected: Returns 3 (or however many you seeded)

Troubleshooting

  • `ERROR: relation "learner" does not exist` while creating enrollment — learner must be created before enrollment in schema.sql — check the statement order.
  • `ERROR: insert or update on table "enrollment" violates foreign key constraint` — seed.sql is inserting an enrollment row before the learner or course row it references — check seed.sql's statement order.
  • `ERROR: duplicate key value violates unique constraint` on an email you only meant to insert once — Check for an accidental duplicate INSERT, or confirm you're not re-running seed.sql against a database that already has that row from a previous run — drop and recreate the database to start clean if needed.

Stuck? Get a hint.

Extension challenge

Add a fourth table, note (id, learner_id REFERENCES learner, lesson_reference TEXT, body TEXT, created_at TIMESTAMPTZ DEFAULT now()), and seed at least two notes -- confirming you correctly place it after learner in the creation and seed order.

When you've verified this locally, use the "Mark lesson complete" button below to record your progress.

Common mistakes

  • Creating a table before the table(s) it references via foreign key -- PostgreSQL cannot validate a reference to a table it doesn't know about yet, and the CREATE TABLE statement fails outright.
  • Seeding data in an order that violates foreign key dependencies -- exactly the same ordering rule as table creation applies to every single INSERT, with no exception for 'just seed data.'
  • Putting a UNIQUE constraint on learner_id and course_id as two SEPARATE column-level constraints instead of one composite UNIQUE (learner_id, course_id) -- separate constraints would incorrectly prevent the same learner from enrolling in ANY second course at all, not just the same course twice.

Knowledge check

Knowledge check

1. Why must the learner table be created before the enrollment table, if enrollment has a column REFERENCES learner(id)?
2. Why does UNIQUE (learner_id, course_id) as ONE composite constraint behave differently from separate UNIQUE constraints on learner_id and course_id individually?
3. Seed data insertion order violates a foreign key dependency. What actually happens?

Takeaway

Both table creation and seed data insertion must respect foreign key dependency order — a table (or row) can never be created before everything it references already exists, and PostgreSQL enforces this on every single statement, with no special exemption for seed data.

Summary

CREATE TABLE statements must run in dependency order: referenced tables before referencing tables. The same ordering applies to seed INSERT statements. A complete schema combines the right types, PRIMARY KEY, NOT NULL, UNIQUE (including composite UNIQUE for pair-level uniqueness), REFERENCES with an explicit ON DELETE behavior, and CHECK constraints for business rules.

References

Your notes

Notes save automatically.

Finished this lesson?

Mark it complete to track your progress and schedule a future review.

Next: Joins and Aggregation, Beyond the Basics →