Everything we have built in this module —the schema, the data, the JOINs, the reports— rests on an assumption we have so far taken for granted: that when loans says member_id = 14, member 14 exists. If that assumption fails, the JOINs silently lose rows, the counts lie and the previous lesson's reports stop being reliable without anyone noticing.

Referential integrity is the guarantee that this does not happen. It is the third rule of the relational model, we stated it in lesson 02-01 and we have been using it implicitly ever since we wrote REFERENCES in the CREATE TABLE. Now it is time to master it: how it is declared, exactly what the manager checks, what should happen when you delete a row that others depend on, how to detect the damage that already exists and —a warning that can save you months of bewilderment— why SQLite protects absolutely nothing unless you ask it to.

With this lesson we close module 2. When you finish it, BiblioRed will not merely have correct data: it will have a schema that actively prevents the data from ceasing to be correct.

1. Orphan rows: where they come from and what they break
2. Declaring a foreign key
3. What the manager checks and when
4. The ON DELETE and ON UPDATE referential actions
5. The five options, compared
6. Choosing the right action in BiblioRed
7. BiblioRed's final schema with its referential actions
8. Composite foreign keys
9. Deferrable constraints
10. SQLite: PRAGMA foreign_keys = ON
11. Detecting and cleaning up orphan rows
12. Validate in the application or in the database?
13. Common mistakes and tips
14. Exercises
15. Conclusion

An orphan row is a row whose foreign key points at something that does not exist. In the BiblioRed spreadsheet we diagnosed in lesson 01-01 there were three ways of creating them, and all three happened daily:

1. Typing a made-up member number. Nobody checked anything: if the librarian typed 77 instead of 17, the row was stored quite happily.
2. Deleting a row that others depended on. When a member let their card lapse, somebody removed their line from the "Members" tab; their fifteen historical loans stayed in the "Loans" tab, pointing into the void.
3. Renumbering. When the members sheet was reordered, the numbers changed and every earlier reference started pointing at a different person. This is the worst of the three, because it leaves no trace: the row is not orphaned, it is wrongly adopted.

What exactly an orphan row breaks:

• INNER JOINs remove it without warning. The loans-per-branch report from lesson 02-05 would simply return less than there is. And since there is no error, nobody investigates it.
• LEFT JOINs keep it with NULL, which produces reports with gaps that somebody will have to explain.
• The totals do not agree with each other. COUNT(*) FROM loans gives 4,312 and the sum of loans per member gives 4,298. And from that point on, trust in the database evaporates.

Referential integrity turns those three ways of creating orphans into immediate errors, at the exact moment they are attempted. That is its value: the problem shows up when it can still be fixed, not six months later.

We already did it in lesson 02-02; now in detail.

Compact and sufficient for simple cases. The manager generates an automatic name for the constraint, something like members_branch_id_fkey.

This is the one we use in BiblioRed, for three reasons:

1. It is mandatory for composite foreign keys (section 8).
2. It lets you name it. When the error fires, the message will say fk_members_branch, not members_branch_id_fkey.
3. It lets you drop and recreate it with ALTER TABLE ... DROP CONSTRAINT fk_members_branch, which is exactly what we will do in section 7.

When you run it, PostgreSQL validates every existing row. If any of them is orphaned, the whole operation fails:

That is exactly what you want: the constraint does not come into force until the data is clean. Section 11 teaches you how to clean it.

The column a foreign key points at must have a primary key or a UNIQUE constraint. You cannot reference just any column:

And it makes sense: if the referenced column could repeat, "point at the row with last name Alsina" would be ambiguous. That is why copies.book_id could point either at books.book_id (primary key) or at books.isbn (the UNIQUE alternate key), even though the former is the sensible choice.

A foreign key imposes checks on both tables, not just on the child. This is the complete picture:

Let's check it on biblioredb. First, from the child side:

Now from the parent side:

And the NULL case, which is allowed:

A null foreign key points at nothing, and that is valid. The referential integrity rule talks about non-null values. If you want the reference to be mandatory, you have to add NOT NULL: that is a separate design decision. In BiblioRed, loans.member_id is NOT NULL (there is no such thing as a loan without a member) while books.author_id is not (works with no cataloged author do exist).

We undo the test insertion:

So far the manager has confined itself to forbidding. But forbidding is not always what you want. If BiblioRed withdraws a whole title from the catalog, having to delete its copies one by one first is absurd: the natural thing is for them to go with it.

The referential actions state what the manager should do with the child rows when the parent row is deleted or changes key:

They are two independent clauses:

• ON DELETE: what to do when the parent row is deleted.
• ON UPDATE: what to do when the value of the parent row's primary key changes.

If you write neither, NO ACTION applies, the standard's default behavior (the one we saw in the previous section).

To experiment without touching BiblioRed, we create two throwaway tables:

Initial state: three items, two categories.

Items A and B have disappeared. The DELETE on one row has deleted three rows in total, and only the first appears in the message. That is the nature of CASCADE: it is powerful and it is silent.

Now let's try SET NULL:

The item survives; it only loses its reference. We clean up the laboratory:

In 99% of cases they behave the same: both prevent the operation. The difference is when the check happens:

• RESTRICT checks immediately, as soon as the DELETE's row is processed.
• NO ACTION checks at the end of the statement, and can also be deferred until COMMIT if the constraint was declared DEFERRABLE (section 9).

Practical consequence: RESTRICT can never be deferred. If you foresee needing deferrable constraints, use NO ACTION. In BiblioRed we will use RESTRICT where we want an explicit prohibition visible in the CREATE TABLE, because it documents the intent better than leaving the slot empty.

The idea is "if this copy's branch disappears, assign it to branch 1". The problem is obvious: if one day somebody deletes branch 1, the action fails and the DELETE is blocked in a way that is hard to diagnose. It exists, you should know about it, and in practice it is used very little. Note: DEFAULT clauses are studied in depth in lesson 04-04.

The question to ask for each foreign key is always the same:

If the parent row disappears, does the child row still make sense on its own?

• If it does not make sense and is worth nothing → CASCADE.
• If it does not make sense but is valuable (history, accounting, audit) → RESTRICT.
• If it does make sense without the reference → SET NULL.

Let's apply it to BiblioRed's eight foreign keys.

Why deleting a member must not drag their loan history with it. A loan is a fact that happened: on 5 March 2026 a copy went out through the door and came back on 2 April with a €1.40 surcharge. That fact does not stop having happened because the person lets their card lapse.

If we set CASCADE, deregistering a member would destroy historical statistics —loans per year, most borrowed titles, revenue— that management uses to decide purchases. It is the destruction of accounting information disguised as tidying up.

With RESTRICT, the deletion attempt fails, and that forces you to settle the real business question: members are not deleted, they are marked as inactive (active = FALSE, like Ramón Etxebarri). That is what is called a soft delete, and it is the correct practice for any entity with a history.

Why deleting a book must drag its copies with it. Here the relationship is one of composition: a copy is a physical copy of a book. EJ-3081 is not "an object that happens to be associated with The Map of Time"; it is a copy of The Map of Time. Without the book, the row means nothing: it would be an object with no title, no author and no ISBN.

If the title disappears from the catalog, keeping its fifteen copies would mean preserving referential rubbish. CASCADE is the right action.

And here something interesting happens. Let's try to delete a book that does have loans:

The CASCADE on copies tries to delete copies 1, 2 and 3… but the RESTRICT on loans.copy_id prevents it:

The cascade stops when it hits a constraint. That is exactly the desired behavior: a title that was never lent can be withdrawn from the catalog, but one with a history cannot. The schema enforces a real business rule without anyone having programmed it into any application.

The asymmetry between loans (RESTRICT) and reservations (CASCADE) is the heart of the reasoning: a loan is history and a reservation is future. History is preserved; a future that can no longer happen is discarded.

All our primary keys are surrogate and, by definition, never change value (lesson 02-01). So ON UPDATE CASCADE will never fire. Why put it there, then?

It is cheap insurance. If one day identifiers have to be renumbered during a migration or a merger of two catalogs, the propagation will be automatic instead of a manual, error-prone script. It costs nothing and averts an unlikely but serious disaster. In a schema with natural keys (where the key really can be corrected) ON UPDATE CASCADE stops being insurance and becomes indispensable.

This is the lesson's deliverable. We replace the eight foreign keys we created in 02-02 with their versions carrying referential actions. Run it on your biblioredb:

Verification:

And a check that the rules really do apply:

Here a practical use of transactions appears for the first time: trying out a destructive operation and undoing it. BEGIN opens the transaction, ROLLBACK cancels it completely. It is the content of lesson 06-01; for now, use it as a safety net.

SQLite does not support ALTER TABLE ... ADD CONSTRAINT. To add referential actions you have to recreate the tables with the full definition. In a SQLite CREATE TABLE it is written the same way:

And always, always, PRAGMA foreign_keys = ON;. We look at it in section 10.

If the parent table's primary key is composite (several columns), the foreign key that references it has to be composite too, with the columns in the same order.

Imagine BiblioRed decides to catalog each copy's exact physical location. Shelves are numbered within each branch: there is a shelf A-12 at Central and another A-12 at North, and they are not the same one. The natural primary key is then the pair:

And copies would reference it like this:

Three rules you need to know:

1. Order matters. FOREIGN KEY (a, b) REFERENCES t (x, y) pairs a with x and b with y. Swapping them creates a different and probably absurd constraint.
2. The table form is mandatory. A constraint affecting two columns does not fit in a single column's declaration.
3. Beware of partial NULLs. By default (MATCH SIMPLE, the standard behavior), if any of the columns is NULL, the constraint is not checked at all. That is: a copy with branch_id = 2 and shelf_code = NULL would pass validation even if no such shelf exists. If you want to require either both or neither, you have to write MATCH FULL.

A design observation: in the example above there is an ON DELETE SET NULL on a composite foreign key that branch_id forms part of… and branch_id is NOT NULL in copies. That makes the action fail in practice. It is a good reminder that composite foreign keys are more delicate than they look, and one of the weighty reasons in favor of simple surrogate keys. These modeling decisions are covered in depth in module 4.

These tables are illustrative: do not create them in biblioredb, they are not part of the course schema.

By default, foreign keys are checked immediately, as each statement runs. That poses a problem in three real situations:

1. Circular references. If table A references B and B references A, you cannot insert the first row of either.
2. Bulk loads in arbitrary order. A data dump that inserts loans before members will fail, even though everything is consistent by the end.
3. Swaps. Exchanging the identifiers of two rows passes through an invalid intermediate state.

The standard's solution is to declare the constraint deferrable: its checks are postponed until the transaction's COMMIT.

Three possible modes:

With the constraint deferred, this works:

If consistency had not been restored by the time COMMIT arrived, the whole transaction is cancelled.

Four warnings:

• RESTRICT can never be deferred. Only NO ACTION accepts DEFERRABLE. It is the practical difference between the two that we announced in section 5.
• Deferred constraints use more memory, because the manager has to remember every pending check until the COMMIT.
• The error appears on commit, not on the guilty statement, which makes diagnosis harder.
• SQLite only supports DEFERRABLE INITIALLY DEFERRED, and only if foreign keys are enabled.

Advice: do not use them by default. They are a tool for specific cases —bulk loads, migrations, circular references—, not a general convenience. Undo the whole experiment if you tried it, so that biblioredb goes back to its state from section 7:

This is probably the most important warning in the lesson, and the easiest to overlook.

SQLite accepts the REFERENCES syntax, stores it in the schema, shows it in .schema… and DOES NOT ENFORCE IT, unless it is explicitly enabled on every connection.

For backward compatibility with old versions, foreign keys are disabled by default. The result is a schema that looks protected and is not:

No error, no warning: a freshly created orphan row, referring to a member 77 who does not exist. Now with the check enabled:

What you need to know:

• It is per connection, not per database. Every time you open sqlite3 or every time your application opens a connection, it has to be run again. It is not stored in the file.
• It cannot be enabled inside a transaction: if you try, it is silently ignored.
• Data-access libraries do not always do it for you. Some ORMs and drivers enable it automatically; others do not. Check it yourself.
• Put it as the first line of all your .sql scripts.

And a specific diagnostic tool, very useful when you inherit a file:

Each line is an existing violation: table, rowid of the guilty row, parent table and index of the foreign key. With no arguments it reviews the whole database. It is the first thing to run when you receive somebody else's SQLite database.

Enabling foreign keys prevents new orphans from being created. It does not fix the ones that already exist: in fact, PostgreSQL will refuse to create the constraint while any remain. We need to detect and clean them first.

Let's reproduce the real scenario: BiblioRed imports the historical loans from the spreadsheet into an intermediate table with no constraints, which is how every migration is done.

It is the anti-join pattern from lesson 02-04, applied to data auditing.

The same question with NOT EXISTS, which is just as valid and somewhat more readable:

And the summary for the project meeting, using what we learned in 02-05:

Two thirds of the rows have problems. That is the figure you take into the meeting with management.

The golden rule: never delete orphans without saving them first. The missing information may be somewhere else, and once deleted it does not come back.

Notice the detail: the INSERT ... SELECT with an INNER JOIN imports only the rows that match. The orphans are left out by construction, with no need for a WHERE to filter them. It is the deliberate use of a property of the INNER JOIN that in other contexts was a hazard.

Now we clean up the experiment to leave biblioredb as it was (the two imported loans would have identifiers 13 and 14):

It is a recurring argument in development teams, and it deserves a reasoned answer.

The argument for the application: the error messages are better ("That member does not exist, would you like to sign them up?" instead of a technical text about fk_loans_member), validation happens before reaching the database, and with an ORM the relationships are already described in the code.

The argument for the database, which is the decisive one:

1. The database is not only yours. Over its lifetime, biblioredb will receive writes from the web application, from the nightly import process, from the script somebody runs in psql at eleven at night, from the graphical administration tool and from the migration three years from now. Each of those paths would have to reimplement the same validations. One of them will not.
2. Data outlives code. Applications are rewritten every five or seven years; data is kept for decades. A rule that lives only in the code disappears with it.
3. Concurrency. Checking in the application "does member 14 exist?" and then inserting the loan leaves a window between the two operations. If in that interval another session deletes member 14, the check was useless. The database verifies inside the operation, with the appropriate locks. This reasoning is developed in lessons 06-01 and 06-02.
4. Bulk operations do not go through the application. An UPDATE of 40,000 rows runs in SQL. No code validation ever sees it.

The conclusion is not "one or the other", but both, in layers:

The application validates for the person: useful messages, forms that guide, errors caught before submitting. The database validates for the data: it is the last line of defense, the one nobody can bypass, the one that is still there when the application changes.

A practical case: BiblioRed validates in the web form that the email address has an email format (the database does not check that well), and the database guarantees that it is UNIQUE and that the branch_id exists (the application cannot guarantee that reliably). Each layer does what it knows how to do.

A final note: CHECK constraints —which verify conditions on the values, such as "the status must be one of these four" or "the return date cannot be earlier than the loan date"— are the other great validation tool in the database. They are covered in depth in lesson 04-04, alongside DEFAULT and the criteria for choosing types.

• Forgetting PRAGMA foreign_keys = ON in SQLite. The schema looks correct and protects nothing. It is trap number one of this lesson, and it has to be repeated on every connection.
• Adding ON DELETE CASCADE for convenience. "That way deleting does not raise errors" is the worst possible reason. A DELETE on one row can take thousands of chained rows with it, without warning and without a recycle bin.
• Deleting entities with a history. Members, customers, products sold are not deleted: they are marked as inactive. RESTRICT is your ally precisely because it forces you to face that decision.
• Confusing RESTRICT with NO ACTION. They behave the same except in one decisive detail: RESTRICT cannot be deferred.
• Using SET NULL on a NOT NULL column. PostgreSQL rejects the definition; in other managers the error appears later, when the deletion is attempted.
• Believing that a null foreign key is an error. It is not: NULL means "points at nobody". If the reference must be mandatory, add NOT NULL.
• Leaving constraints unnamed. The day you have to do a DROP CONSTRAINT, you will have to go and look up the automatic name in the catalog.
• Referencing a column with no UNIQUE. The manager rejects it, and rightly so: the reference would be ambiguous.
• Deleting orphans without saving them. Always move them to a quarantine table first. What is deleted does not come back.
• Relying on application validation alone. There will be another write path. There always is.
• Tip: document the referential action you chose with a comment in the CREATE TABLE. Two years from now, "why is this one RESTRICT and that one CASCADE?" will be a real question, and the answer is a business decision, not a technical one.
• Tip: when you inherit a database, the very first thing to run is the orphan audit for each foreign key (or PRAGMA foreign_key_check in SQLite). In thirty seconds it will tell you what quality of data you are working with.

BiblioRed wants to add three new tables. For each foreign key, decide the ON DELETE and justify the choice in one sentence.

1. reviews (review_id, member_id, book_id, content, rating, review_date): reviews written by members about the books.
2. fines (fine_id, loan_id, amount, issue_date, paid): financial penalties arising from a loan.
3. events (event_id, branch_id, title, event_date): cultural activities organized by each branch.
4. registrations (registration_id, event_id, member_id, registration_date): members signed up for those events.

With the final schema from section 7 and the course data set, say what happens with each statement and how many rows are affected in total.

1. DELETE FROM authors WHERE author_id = 8; (Marina Escolá, no works)
2. DELETE FROM authors WHERE author_id = 4; (Óscar Barreda, two works)
3. DELETE FROM books WHERE book_id = 339; (Ensanche Records, 1 copy with no loans)
4. DELETE FROM books WHERE book_id = 331; (The Map of Time, 3 copies, 4 loans, 2 reservations)
5. DELETE FROM members WHERE member_id = 20; (Elena Roig, no loans and no reservations)
6. DELETE FROM members WHERE member_id = 16; (Nuria Bastos, 2 loans, 1 reservation)
7. DELETE FROM branches WHERE branch_id = 4; (East, 1 member, 2 copies)

BiblioRed has received a file of members from another library to take on. Create it as an intermediate table:

1. Write a query that detects the rows whose branch_id does not exist.
2. Write a query that detects the rows whose email is already in use by a current member.
3. Write an audit report with a diagnosis column that classifies each row.
4. Import only the valid rows and check how many got in. Afterwards, leave biblioredb as it was.

Solution 1

The pattern that emerges: financial and historical facts → RESTRICT; intentions and accessory items → CASCADE; content with value of its own → SET NULL.

Solution 2

An important observation about cases 4, 6 and 7: the failure is atomic. Even if the cascade had started deleting rows before hitting the RESTRICT, everything is undone: the statement is a unit. That is guaranteed by transactions, the subject of lesson 06-01.

Solution 3

Note: row 6 violates no foreign key —NULL is a valid reference— but it would violate the NOT NULL on members.branch_id. They are two different constraints and both have to be audited.

Rosa Cabanes and Amina Bakri get in. The INNER JOIN with branches discards rows 2, 5 and 6 by construction, and the NOT EXISTS discards row 4.

We close the module with the guarantees that keep everything above true as time passes:

• An orphan row is a foreign key pointing at something that does not exist. They were born daily in BiblioRed's spreadsheet through typing, deletions and renumberings, and their damage is silent: INNER JOINs remove them without warning and the totals stop adding up.
• A foreign key is declared in column form or, better, in table form with a proper name (CONSTRAINT fk_...), and it can only reference columns with PRIMARY KEY or UNIQUE. When you add it with ALTER TABLE, the manager validates every existing row.
• The manager checks on INSERT and UPDATE of the child table, and on DELETE and UPDATE of the parent table's primary key. A null foreign key is legal: if the reference must be mandatory, you have to add NOT NULL.
• The five referential actions —NO ACTION, RESTRICT, CASCADE, SET NULL and SET DEFAULT— define what happens to the children when the parent disappears. RESTRICT cannot be deferred; that is its only real difference from NO ACTION.
• The choice criterion in BiblioRed: a loan is history (RESTRICT) and a reservation is future (CASCADE); a copy is part of a book (CASCADE) and a book outlives its author's record (SET NULL). A member is not deleted: they are marked as inactive.
• Composite foreign keys require the table form, respect the column order and, with MATCH SIMPLE, are not checked if any column is null.
• Deferrable constraints (DEFERRABLE INITIALLY DEFERRED) postpone the check to COMMIT, and they exist for circular references, bulk loads and swaps. They are not a general convenience.
• SQLite does not enforce foreign keys unless PRAGMA foreign_keys = ON is enabled, on every connection. PRAGMA foreign_key_check audits a whole database.
• The orphans that already exist are detected with the LEFT JOIN ... IS NULL pattern (or NOT EXISTS), classified with CASE WHEN, kept in quarantine and only then discarded. An INSERT ... SELECT with an INNER JOIN imports only what is valid, by construction.
• And the underlying conclusion: you validate in both layers. The application validates for the person; the database is the last line of defense, because there will be other write paths, because data outlives code and because only the manager can verify inside the operation, with no concurrency windows.

That brings module 2 to a close. You have travelled the complete road of the relational world: the theory of the model and its integrity rules, the SQL language and the creation of the schema, CRUD on a single table, joining several tables with JOINs and subqueries, summarizing with aggregates and grouping, and the referential guarantees that hold it all up. biblioredb is no longer an empty database: it is an information system with seven tables, consistent data, management reports and defenses of its own. In module 3, Non-Relational Databases, we change worlds: we will see what NoSQL is, which families exist, how data is modeled when there is no fixed schema and no foreign keys to protect it —and what is gained and what is lost in that bargain—. BiblioRed comes with us: its reviews and its activity log are, as we decided in lesson 01-02, the perfect use case for MongoDB.