A relationship has two questions in each direction: how many related rows are allowed, and whether any are required. A project may have zero or many members; a user may join zero or many projects. Those are many-to-many maximum cardinalities with optional participation. Translating only “many” loses the minimum, while translating only “required” loses how many associations are allowed.
For a many-to-one relationship, place a foreign key on the many side. A required project.owner_id can reference users(id): each project has one owner, and one user can own many projects. NOT NULL supplies the minimum on that child reference. The foreign key establishes parent existence. Neither rule requires every user to own a project. Requiring at least one child for every parent is a different invariant that ordinary child foreign keys do not enforce.
For many-to-many, give the association a table. A membership row says “this user belongs to this project” and may carry a role or joining time. These attributes depend on the association. A role on users would incorrectly give someone the same role in every project; a role on projects would give every member the same role.
The composite key matters. PRIMARY KEY(user_id,project_id) forbids the same association twice while allowing either endpoint to repeat with another partner. UNIQUE(user_id) would silently change the model to at most one project per user. Adding a surrogate membership ID would still require UNIQUE(user_id,project_id) if duplicate associations are forbidden.
CREATE TABLE users(id INTEGER PRIMARY KEY);
CREATE TABLE projects(id INTEGER PRIMARY KEY);
CREATE TABLE memberships(
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
role TEXT NOT NULL CHECK(role IN ('viewer','editor')),
PRIMARY KEY(user_id,project_id)
);
INSERT INTO users VALUES(1),(2);
INSERT INTO projects VALUES(10),(20);
INSERT INTO memberships VALUES(1,10,'editor'),(1,20,'viewer'),(2,10,'viewer');
DELETE FROM projects WHERE id=10;
SELECT user_id,project_id,role FROM memberships ORDER BY user_id,project_id;A one-to-one maximum can be modeled by a UNIQUE child foreign key. If the reference is nullable, SQLite's uniqueness allows several unassigned child rows; each non-NULL parent ID still occurs at most once. Requiredness needs a separate decision. Always state the minimum and maximum in ordinary words before selecting these constraints.
Deletion policy follows lifecycle, not diagram aesthetics. A membership has no meaning without either endpoint, so this lab cascades endpoint deletion into memberships. Deleting a project must not delete its users. The arrows in the second diagram show that direction. By contrast, an invoice may need to restrict customer deletion to retain business history; cascading every reference is not a universal cleanup strategy.
The build tests empty membership sets, both directions of “many,” duplicate pairs, unknown endpoints and invalid roles. It also checks the declared actions and performs a rolled-back deletion probe. Repair the two bugs by restating the relationship each faulty line actually models. That explanation is more useful than memorizing a composite-key template.