From 3NF to a Running Table: CREATE TABLE with Constraints

Turn a 3NF schema into real Postgres DDL — PRIMARY KEY, FOREIGN KEY ... REFERENCES, NOT NULL, CHECK, DEFAULT — and confirm the database itself enforces entity and referential integrity.

Where logical design meets the engine

A 3NF schema on paper becomes a running database through DDL — CREATE TABLE statements that declare the very constraints from Lesson 4.2. The point of this lesson: integrity is enforced by Postgres, not by the application. Once you declare the constraint, *no* program can bypass it.

The constraint vocabulary you write into CREATE TABLE:

- PRIMARY KEY — enforces entity integrity: the column(s) are unique *and* implicitly NOT NULL. - FOREIGN KEY (...) REFERENCES parent(pk) — enforces referential integrity: a child value must match a parent PK (or be null), with optional ON DELETE CASCADE / RESTRICT / SET NULL. - NOT NULL — a column must always have a value. - CHECK (...) — a domain constraint: a row is rejected unless the predicate holds (e.g. gpa BETWEEN 0 AND 4). - DEFAULT — a value supplied when the INSERT omits the column.

Worked example: build the decomposed registrar tables

Here is the GRADE_REPORT 3NF decomposition expressed as real Postgres, with full constraints. We give the tables fresh names (reg_student, reg_course, reg_instructor, reg_grade) so they do not collide with the seeded schema:

CREATE TABLE reg_instructor (
  instructor_name     text PRIMARY KEY,
  instructor_location text NOT NULL
);
CREATE TABLE reg_student (
  student_id     int PRIMARY KEY,
  student_name   text NOT NULL,
  campus_address text,
  major          text DEFAULT 'Undeclared'
);
CREATE TABLE reg_course (
  course_id       int PRIMARY KEY,
  course_title    text NOT NULL,
  instructor_name text REFERENCES reg_instructor(instructor_name)
);
CREATE TABLE reg_grade (
  student_id int REFERENCES reg_student(student_id),
  course_id  int REFERENCES reg_course(course_id),
  grade      text CHECK (grade IN ('A','B','C','D','F')),
  PRIMARY KEY (student_id, course_id)
);

The composite PRIMARY KEY (student_id, course_id) in reg_grade directly encodes the entity-integrity rule (neither component may be null), and the two REFERENCES clauses make Postgres reject any grade row that points at a nonexistent student or course. The CHECK enforces the grade domain; the DEFAULT fills major when omitted.

Proving the constraints fire

Insert a *valid* row and it succeeds; insert one that violates referential integrity (a course_id with no parent) and Postgres rejects it. That rejection is the whole value of declaring constraints in the schema — the database is the last line of defense, independent of any application code.