Schemas and Constraints

Inserting, Updating, and Deleting Under Constraints

---

We learned about the basic shapes of INSERT, UPDATE, and DELETE in the part Working with Single-Table Data. In this part, the commands are the same, but the schema is richer. The schema now has foreign keys, CHECK constraints, UNIQUE rules, and referential actions. A write operation can be syntactically correct and still fail for a good reason.

Inserts Must Respect the Schema

Consider inserting an exercise:

INSERT INTO exercises (course_instance_id, title, deadline_at)
VALUES (3, 'SQL joins practice', '2026-10-01 23:59:00');

This only succeeds if:

• all NOT NULL columns are supplied (or have defaults),
• the values fit the column types,
• the referenced course instance exists in course_instances,
• no CHECK constraint rejects the values.

That last point is especially important. The foreign key changes what counts as a valid insert. A row that would have been acceptable in a schema with no relationships may now be rejected because it refers to a row that does not yet exist.

It also affects order. If the referenced course instance does not exist yet, then the insert should happen later, after the needed parent row has been created. In seed data, this means INSERT statements have to follow the dependency order just as CREATE TABLE statements do.

Loading Exercise...

Insert Order for Seed Data

A typical seed for the course-platform schema might look like this:

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');

Every INSERT respects the order imposed by the schema. courses and users come first because they do not reference anything. course_instances comes after courses. exercises comes after course_instances.

One subtle point: the inserts above rely on course_instances.id = 1 being assigned to the first row. That is how SERIAL works on a fresh database, but the dependency is implicit. The next chapter that focuses on Migrations and Seed Data shows a more resilient pattern that looks up identifiers with subqueries rather than assuming them. For the example here, the shorter version is fine; the next chapter explains when to prefer the longer one.

Updates Must Respect Constraints Too

UPDATE submissions
SET score = 18,
    status = 'graded'
WHERE id = 14;

This still follows the familiar UPDATE ... SET ... WHERE ... pattern, but the database may reject the change if it violates a constraint.

For example, now, the course-platform schema has status TEXT NOT NULL CHECK (status IN ('submitted', 'graded', 'late')). So:

UPDATE submissions
SET status = 'mystery-state'
WHERE id = 14;
-- expected: ERROR: new row for relation "submissions" violates check constraint

will fail even though the WHERE clause matches a real row. The CHECK constraint applies to the new value, not only to inserts.

The same applies to foreign keys:

UPDATE submissions
SET exercise_id = 999
WHERE id = 14;
-- expected: ERROR if no exercise with id = 999 exists

An update that would leave the row pointing to a non-existent exercise is rejected.

Loading Exercise...

Deletes Become More Interesting

Deletes are where referential actions come into play.

Consider deleting a course instance that still has exercises and enrollments:

DELETE FROM course_instances
WHERE id = 1;

Depending on the ON DELETE clause on the references to course_instances, this statement may:

• succeed and cascade the delete to all referencing rows,
• fail with a foreign-key violation if any referential action is RESTRICT or NO ACTION,
• succeed and set the referencing columns to NULL if any referential action is SET NULL.

The previous chapter’s verification step already introduced this pattern for the recipe app and the library. In write-heavy workflows, predicting which of the three outcomes will happen is the difference between confident changes and nervous ones.

Loading Exercise...

Before-and-After Table State

Suppose the submissions table begins like this:

id	exercise_id	user_id	score	status
14	7	22	NULL	submitted

After a grading update, the state becomes:

id	exercise_id	user_id	score	status
14	7	22	18	graded

The change is small, but every constraint the schema defined still held through the update. NOT NULL was not violated, CHECK (status IN ...) accepted the new value, and the foreign keys still point to real rows. The row that came out the other side is valid under exactly the same rules as the row that went in.

A Safe Working Pattern Still Helps

Even with richer schemas, the safe pattern from the second part of the course still applies:

1. identify the row carefully,
2. check what other rows may depend on it,
3. run the modification,
4. verify the new state.

This is still a practical skill, even when constraints now provide some additional protection. For example, before deleting a course instance, it is sensible to check whether exercises or enrollments still depend on it. The database may reject the delete anyway, but predicting that rejection is part of good schema-aware work.

For write-heavy flows, this pattern can be extended:

-- 1. check what rows match
SELECT id, course_id, term, year FROM course_instances WHERE id = 1;

-- 2. check what depends on them
SELECT COUNT(*) FROM exercises WHERE course_instance_id = 1;
SELECT COUNT(*) FROM enrollments WHERE course_instance_id = 1;

-- 3. decide whether to proceed, and only then run the delete
DELETE FROM course_instances WHERE id = 1;

The small overhead of the checking queries is usually worth it. They make the intent visible and surface surprises before the delete runs.

Loading Exercise...

Predicting Write Outcomes

Once the schema has several constraints, predicting which writes succeed and which fail becomes a practice skill of its own.

Consider again the course-platform schema and these attempted writes. Before reading further, try to predict the outcome of each: will it succeed, fail, or succeed with cascading effects?

1. INSERT INTO users (name, email, role) VALUES ('Aino', 'aino@example.com', 'student'); — twice in a row.
2. INSERT INTO submissions (user_id, exercise_id, status) VALUES (99, 1, 'submitted'); when no user 99 exists.
3. UPDATE submissions SET score = -1 WHERE id = 1; against the CHECK (score IS NULL OR (score >= 0 AND score <= 100)) from Chapter 4.
4. DELETE FROM exercises WHERE id = 1; against the ON DELETE CASCADE on submissions.exercise_id from Chapter 4.

Commit to your predictions first. The exercise below asks you to walk through these cases.

Loading Exercise...

Walkthrough

Now let’s trace each write through the constraints.

The first one succeeds on the first attempt and then fails on the second because of the UNIQUE on email in Chapter 4’s users schema.

The second fails because of the foreign key on user_id: there is no user 99 for the submission to reference.

The third fails because of the CHECK on score: a negative value violates the score >= 0 branch, and -1 is not NULL, so the IS NULL OR ... guard does not save it either.

The fourth succeeds, and it also removes the related submissions. The ON DELETE CASCADE on submissions.exercise_id means that when exercise 1 disappears, every submission that referenced it is deleted in the same operation.

Getting these right before running them is the difference between confident schema work and trial-and-error schema work. If one of the predictions surprised you, that is a useful signal: the schema and your mental model of it disagree somewhere, and practising with the schema is a good way to pin down where.

A Small Worked Sequence

Consider a full lifecycle of a submission in the course-platform schema:

-- 1. A student submits work.
INSERT INTO submissions (user_id, exercise_id, status)
VALUES (1, 1, 'submitted');
-- submitted_at defaults to now, score stays NULL.

-- 2. The submission is visible as an ungraded submission.
SELECT id, submitted_at FROM submissions
WHERE exercise_id = 1 AND score IS NULL;

-- 3. A teacher grades it.
UPDATE submissions
SET score = 9, status = 'graded'
WHERE id = 1;

-- 4. The teacher confirms the update.
SELECT id, score, status FROM submissions WHERE id = 1;

Every step respects the schema. Every step is a recognizable pattern that the application will later implement in route code. Walking through the lifecycle in plain SQL first makes the later application code easier to reason about.

Loading Exercise...

Check Your Understanding

1. Why can an UPDATE fail even though the WHERE clause matches a real row?
2. Why does the order of INSERT statements in seed data matter?
3. Why is it useful to predict the outcome of a write before running it?

SQL Programming Exercise

Here, you get to practise writing INSERT, UPDATE, and DELETE statements against a schema with constraints. The first exercise is a simple insert, the second an update, and the third a delete. In each, case, first write a query to predict the outcome, and then run the write to see if your prediction was correct.

Loading Exercise...