Schemas and Constraints

Verifying the Schema

---

A schema that looks correct on paper is not necessarily correct. Some problems only show up when evaluated with real data.

The chapter Testing a Design Against Its Questions from the previous part did the conceptual evaluation by walking through the ER diagram with the questions it was supposed to support. Here, the verification step has the same idea at a lower level: now that the schema is real SQL, the questions can actually be run, and the constraints can actually be tested.

Two Kinds of Verification

Verification at this stage usually covers two kinds of checks.

Query verification asks: does the schema let me write each expected query cleanly? If yes, the structural work is probably solid. If not, something is missing or in the wrong place.

Constraint verification asks: do the constraints I wrote actually do what I think? This is where the database becomes interactive. Writing a CHECK constraint is not the same as knowing it works, and the only reliable way to find out is to try an insert that should fail.

Both kinds of verification are cheap at this stage. A missing column is trivial to add if the database is empty and no application depends on it yet. The same change made later, after data and code have accumulated, is much more expensive.

Query Verification: Walking the Question List

The subject-analysis note from the previous part included a list of likely application queries. For the course-platform example, these were:

• which exercises belong to a given course instance?
• which students are enrolled in a given course instance?
• which submissions still need grading?
• what is the most recent submission from a given student for a given exercise?
• which students are enrolled in a course instance but have not submitted any work?
• what is the average score for a given exercise?

For each question, write the actual SQL query and try it against the schema (add data to the database so that you have something to test against). If a query cannot be written, something is missing. If the query runs but returns results that do not match the question, the query or the schema is off.

For example, the “most recent submission” question translates to:

SELECT id, submitted_at, score, status
FROM submissions
WHERE user_id = 1
  AND exercise_id = 7
ORDER BY submitted_at DESC
LIMIT 1;

If the schema did not have a submitted_at column, this query could not be written. The verification step would surface that gap immediately.

Loading Exercise...

Constraint Verification: Trying Invalid Inserts

Writing a constraint is an assertion that certain data will be rejected. The only way to confirm the assertion is to try inserting data that should be rejected and observing that the insert fails.

For example, the Constraints and Integrity Rules chapter has the following constraint for the course-platform schema:

status TEXT NOT NULL CHECK (status IN ('submitted', 'graded', 'late'))

So trying to insert a submission with status = 'mystery' should fail. Running the insert is the verification.

INSERT INTO submissions (user_id, exercise_id, status)
VALUES (1, 7, 'mystery');
-- expected: ERROR: new row for relation "submissions" violates check constraint

If the database accepts the row, something is wrong. Perhaps the constraint was written but never applied. Perhaps the constraint expression was slightly wrong. Either way, the mismatch is now visible, and it can be fixed before the schema starts being used for real data.

Loading Exercise...

One Invalid Insert Per Constraint

For a schema with several constraints, a useful verification habit is to try one invalid insert per constraint:

• insert with a NULL in a NOT NULL column — should fail,
• insert with a duplicate value in a UNIQUE column — should fail,
• insert with a foreign key value that does not match any row in the referenced table — should fail,
• insert with a value that violates a CHECK condition — should fail,
• insert with omitted optional columns — should succeed and use defaults,
• insert with a valid full set of values — should succeed.

Going through this menu takes only a few minutes and catches a large fraction of constraint mistakes. It also teaches what each error message looks like, which saves time when the same error shows up later in application code.

Verifying Referential Actions

Referential actions deserve their own verification step because they do not appear in ordinary INSERT statements. They fire on DELETE or UPDATE of a referenced row.

For the recipe app DDL from the previous chapter, useful checks include:

• delete a user and verify that the user’s recipes are also deleted (cascade),
• delete a recipe and verify that its recipe_ingredients rows are gone,
• try to delete an ingredient that is used by any recipe and verify that the delete is blocked,
• delete a favorite and verify that neither the user nor the recipe is affected.

These checks are quick to run and make the cascade behavior concrete. Before running them, it is useful to predict the outcome. Predicting correctly means the design is clear; predicting incorrectly means there is something to learn.

Loading Exercise...

Walking the Course-Platform Schema

A compact verification session for the course-platform schema might look like this.

First, confirm the basic inserts work:

INSERT INTO courses (code, title)
VALUES ('CS-A1150', 'Introduction to Databases');

INSERT INTO users (name, email, role)
VALUES ('Aino', 'aino@example.com', 'student');

INSERT INTO course_instances (course_id, term, year)
VALUES (1, 'Spring', 2026);

INSERT INTO exercises (course_instance_id, title, deadline_at)
VALUES (1, 'SQL Basics', '2026-03-10 23:59');

Then confirm that a violated foreign key fails:

INSERT INTO exercises (course_instance_id, title, deadline_at)
VALUES (999, 'Orphan', '2026-03-10 23:59');
-- expected: ERROR: insert or update on table "exercises" violates foreign key constraint

Then confirm that a CHECK constraint fires:

INSERT INTO submissions (user_id, exercise_id, status)
VALUES (1, 1, 'almost-submitted');
-- expected: ERROR: new row for relation "submissions" violates check constraint

Then confirm that the UNIQUE constraint on courses.code fires:

INSERT INTO courses (code, title)
VALUES ('CS-A1150', 'Another Course');
-- expected: ERROR: duplicate key value violates unique constraint

Finally, confirm that a valid insert still works:

INSERT INTO submissions (user_id, exercise_id, status)
VALUES (1, 1, 'submitted');
-- expected: success
SELECT * FROM submissions WHERE user_id = 1 AND exercise_id = 1;
-- expected: one row with an automatic submitted_at value

This entire session takes a few minutes and turns every line of the schema into something that has been confirmed to behave as intended.

What to Do When Verification Fails

When a verification check fails in an unexpected way, there are three usual explanations.

The schema does not match intent. The DDL was written slightly wrong. A CHECK expression has a typo, a foreign key points to the wrong table, a NOT NULL is missing. The fix is in the DDL.

The intent was unclear. The test exposed that the design package did not actually decide what should happen in this case. The fix is to resolve the question in the design rationale first, then update the DDL to match.

The test itself is wrong. The insert you thought was invalid is actually a legitimate case the schema should accept. The fix is to rewrite the test, possibly after realizing the constraint was too strict.

All three failure modes are useful. The first finds bugs in the DDL. The second finds incomplete design. The third finds over-restrictive assumptions. Each is worth catching before the schema starts being used for real data.

Loading Exercise...

Loading Exercise...

Check Your Understanding

1. Why is walking the expected queries against a new schema useful even before any application code is written?
2. Why does constraint verification require intentionally trying invalid inserts rather than only checking that valid ones work?
3. What are the three usual explanations when a verification check fails unexpectedly?

SQL Programming Exercise

Next, you get to practise verifying schema. The first exercise is a simple query verification check, while in the second, you first delete a row and then check the remaining rows.

Loading Exercise...