SQL from zero · Chapter 22 of 37
Rules the database enforces
By the end of this chapter you can make the database refuse bad rows, and decide what happens to the rows that point at a row you delete. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Building the schema
- 4 tasks
- SQL
- In the all labs plan
The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.
Why you need this
Your circles table is in staging, and Rohan has already broken it: a circle owned by user 999 (there is no user 999), Chai Lovers twice for the same owner, and a member limit of minus four. Next, Meera wants a table of who is in which circle.
Last chapter's table only checks types and NOT NULL, so all three rows went in. You could write a SELECT that hunts for bad rows every morning and fix them by hand, but by then the app has already shown them. The app could check, but scripts and psql skip the app. A rule in the table holds for every way in.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
circles.sql: circles with a real owner, unique names and a member limit
Create circles again with the same eight columns, types and defaults as last chapter (id, name, description, owner_id, is_private, member_limit, created_at, archived_at), now with three rules.
owner_id must be the id of a real member, and when that member is deleted, their circles are deleted too. One owner cannot have two circles with the same name, but two different owners can. member_limit must be from 2 to 500: 2 and 500 are allowed, 1 and 501 are refused.
Start by dropping circle_members and circles if they exist.
- Task 2
circle-members.sql: a member joins a circle only once
Create circle_members, one row per member of a circle, with exactly four columns: circle_id (integer, required), user_id (integer, required), role (text, required) and joined_at (timestamptz, required, the moment of the insert unless given).
The primary key is the pair circle_id and user_id: the same member cannot join the same circle twice, but can join other circles, and a circle can have many members.
- Task 3 · interview question
Interview: circle-members.sql, what deleting a circle or a member does
In the same circle-members.sql: circle_id must be a real circle, and user_id must be a real member.
Deleting a circle deletes its memberships with it. Deleting a member who is still in a circle is refused.
- Task 4
circle-members.sql: the role is owner, admin or member
In the same circle-members.sql: role must be exactly one of owner, admin and member (in lower case), and is member when an insert leaves it out.
Afterwards, moderator and Admin (capital A) are refused, and so is a role that is NULL.
What you will learn
- PRIMARY KEY and UNIQUE: no two rows with the same value, on one column or on a pair.
- FOREIGN KEY (REFERENCES): the value must exist in another table.
- CHECK: the row must make a condition true.
- ON DELETE CASCADE and ON DELETE RESTRICT: what happens to the rows that point at a row you delete.
More in Building the schema
Build "Rules the database enforces" yourself
MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.