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.
- 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.
- 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.
- 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.
- 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.