Postgres in depth · Chapter 10 of 65

Rules the data must follow

By the end of this chapter you can give a table rules that Postgres enforces on every row: primary keys, NOT NULL, UNIQUE and CHECK, with names you choose. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Designing LCO properly
  • 4 tasks
  • PostgreSQL
  • In the all labs plan

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.

Why you need this

The day-one tables accept anything. In chapter 8 you saw the results: Sana found two students with the same email, a course with no price at all, and a level typed as Intermediate with a capital I that every filter on 'intermediate' missed. Each one was a mistake somebody made once, and the table said nothing.

The website checks some of this, but the website is not the only thing that writes to LCO's database: Priya's scripts, Arjun's imports and a tired person in psql at midnight all do. A rule is only a rule if it lives in the table, where every writer meets it.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    students.sql: the students table, with rules

    Create LCO's students table, called students:

    id: a bigint identity generated by default, and the primary key. name: text, required. email: text, required, and no two students may share one. phone and city: text, optional. signed_up_at: a moment with the time zone, required.

  2. Task 2

    courses.sql: courses, rebuilt with rules

    Replace chapter 9's courses table (it already exists and is empty) with one that has rules. Columns:

    id: bigint identity generated by default, the primary key. title: text, required. slug (the course's name in its web address, like react-from-zero): text, required, unique. instructor: text, required. level: text, required, default 'beginner', and only 'beginner', 'intermediate' or 'advanced'. price: numeric(10, 2), required, default 0, never negative, in a rule named courses_price_not_negative. is_published: boolean, required, default false. published_at: timestamptz, optional. created_at: timestamptz, required.

  3. Task 3

    coupons.sql: coupons that cannot give too much away

    Create the coupons table, called coupons:

    id: bigint identity generated by default, the primary key. code: text, required, unique. percent_off: integer, required, from 1 to 100 (both allowed). max_uses: integer, optional (NULL means no limit). used_count: integer, required, default 0. valid_from and valid_until: timestamptz, both required.

  4. Task 4 · interview question

    Interview: referrals.sql, one link per campaign

    Create referral_links: id (bigint identity generated by default, the primary key), student_id (bigint, required), campaign (text, optional: NULL means the student's everyday link) and code (text, required, unique).

    The rule: a student has at most one link per campaign, and at most one everyday link (campaign NULL). Different students may each have their own everyday link.

What you will learn

  • Primary keys: the one column (or columns) that names each row.
  • NOT NULL and DEFAULT: a value that must be there, and the one used when none is given.
  • UNIQUE: no two rows alike, and what it does with NULL (NULLS NOT DISTINCT).
  • CHECK: any true-or-false rule about a row.
  • Named constraints: errors that say which rule was broken.

More in Designing LCO properly

Build "Rules the data must follow" yourself

LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.