Constraints: Rules the Database Can Enforce

11.1.1 PLAIN — in simple words#

1. A constraint is a rule the engine checks when relevant data is written. If the operation would violate an enforced rule, the engine reports a failure instead of accepting that prohibited result.
2. NOT NULL says that a column must contain a supplied value rather than SQL’s missing-value marker. It does not say that a text value is nonempty, a number is positive or the information is truthful.
3. CHECK tests an expression. For example, a supplied agreed quantity must be between 1 and 10,000 in our storage adapter.
4. These are different jobs. A check such as “value greater than zero” is not a complete way to require a value, because a missing value does not behave like the number zero.
5. In the SQLite examples, a check that evaluates to NULL is not a violation. Use an explicit presence rule when absence is prohibited. [S61]
6. The same field can need several rules at once: required, appropriate storage type and within the permitted range. None of those independently proves the real-world claim behind the record.
7. Treat a successful insert as evidence that the applicable enforced rules accepted it. Do not translate it into “every requirement was checked” unless every relevant requirement actually has a mechanism.

11.1.2 PLAIN — a picture in your head#

1. Imagine an entry desk with separate questions. First: “Did you supply the required document?” Second: “Does its stated number fall within the accepted range?”
2. Skipping the first question cannot be repaired merely by looking at the range on documents that arrived. An absent document is not a document showing zero.
3. Similarly, “a value must be present” and “a supplied value must be positive” should be written as separate promises when both matter.
4. A clear label on the desk is helpful, but it is not the same as someone actually checking the condition. A comment in SQL resembles the label; a declared constraint resembles the implemented check.
5. Where the comparison breaks: SQL’s truth rules are precise, not a human clerk’s intuition. A CHECK expression and a WHERE filter treat an unknown result differently for their respective purposes.
6. The entry-desk picture should therefore lead to actual truth cases, not replace them. Test missing, boundary, valid and invalid values directly.

11.1.3 PLAIN — a worked example#

1. The new lab creates two isolated tables. They are not modifications to order_lines.

CREATE TABLE check_only (
    value INTEGER CHECK (value > 0)
) STRICT;

CREATE TABLE required (
    value INTEGER NOT NULL CHECK (value > 0)
) STRICT;

2. Try the following inputs. “Accepted” means accepted by these deliberately small definitions, not approved as a complete shop record.

3. For the original agreed quantity, the stronger declaration uses INTEGER NOT NULL together with the chosen positive range. For the separate draft quantity, absence is permitted but a supplied value must satisfy the same range.
4. An empty text value '' is not SQL NULL. A field needing nonempty text must specify that condition; NOT NULL alone does not express it.
5. Nor does NOT NULL distinguish an honest zero price from a made-up zero inserted to hide an absent quotation. The schema can enforce the stored representation, but the input workflow must preserve meaning.
6. These tests are small enough to reason through before running them. The surprising NULL case is exactly why rules should be demonstrated rather than memorised from their English names.

11.1.4 PLAIN — what is really happening inside#

1. The engine receives a proposed row, applies the relevant type handling and evaluates its declared conditions. It does not infer additional rules from neighbouring prose.
2. An ordinary comparison involving NULL often produces an unknown result rather than true or false. The check-only example therefore does not evaluate NULL > 0 as false.
3. The purpose of a CHECK constraint is to exclude rows that violate its expression under the engine’s constraint semantics. The purpose of a WHERE filter is to retain rows whose predicate is true. These are related uses of expressions with different acceptance decisions.
4. The later SQL chapter demonstrates the distinction: a NULL quantity can pass a nullable positive CHECK but will not be selected by WHERE quantity > 0.
5. A check can relate columns of the same row. An optional pair can require both components absent or both present. This remains a row rule even though it mentions two columns.
6. An arbitrary rule about totals across many rows is different. Do not hide a query inside a check and assume that all future changes elsewhere will trigger a correct re-evaluation. The whole-system invariant needs an appropriate mechanism.

11.1.5 TECHNICAL — the engineer’s version#

1. The lab’s row-domain checks distinguish three boundaries: original runtime input validation, SQLite storage acceptance and business interpretation. Python type distinctions lost during binding cannot be reconstructed from a stored integer alone.
2. In SQLite, CHECK failure is based on an expression result interpreted numerically as zero; a NULL result is not a constraint violation. PostgreSQL’s Boolean CHECK semantics also allow an unknown result, but expression typing is not identical between the engines. [S61] [S02]
3. Prefer direct, explicit declarations for introductory invariants. quantity INTEGER NOT NULL CHECK (quantity BETWEEN 1 AND 10000) states presence and range without relying on a reader to infer either from a column name.
4. Use a separate text predicate when needed. For example, a local nonempty-label exercise could use CHECK (length(label) > 0) with NOT NULL. That still does not define whitespace policy, identity normalization or permitted characters; those require additional decisions.
5. Boundary testing should include values just below and above the accepted interval. For agreed quantity, 0 and 10,001 fail while 1 and 10,000 pass. For agreed price, 0 is allowed, -1 and 100,000,001 are not.
6. Requiring an integer in SQLite STRICT storage does not prohibit every incoming textual representation. The documented lossless-coercion rules and the lab’s observed "2" example keep that distinction explicit. [S60]
7. Avoid volatile or external truth hidden behind a row check. PostgreSQL’s documentation explains why CHECK conditions should not depend on changing data in other rows. Such a function can make the declaration appear stronger than the maintained invariant actually is. [S02]
8. The tests establish selected acceptance cases on the recorded SQLite runtime. They do not prove that every engine, driver or future schema revision applies identical coercion and error behaviour.

11.1.6 WORDS — remember these#

1. Constraint: a rule enforced at a defined database boundary — a declared condition whose violation prevents a relevant operation from succeeding under the engine’s semantics.

2. Not-null rule: a requirement that a value be present — a column condition excluding SQL NULL, distinct from nonempty text or positive numeric value.

3. Check predicate: an expression used to reject prohibited row values — a condition evaluated under an engine’s CHECK semantics.

4. Unknown result: a comparison that cannot be classified as true or false from the supplied values — the third truth outcome commonly produced by SQL expressions involving NULL.

5. Boundary test: a test at an acceptance limit — a selected case at, immediately below or immediately above a declared domain boundary.

11.2.1 PLAIN — in simple words#

1. A uniqueness rule prevents repeated values, or repeated combinations of values, under its declared scope and comparison rules.
2. A primary key is the chosen key used to identify rows. In our teaching tables it is required and unique. Some tables use one column; order lines use a pair.
3. A key on (order_id, line_no) prevents two records with exactly the same complete line identity. It does not prohibit line number 1 on two different orders.
4. It also does not say that two separate lines cannot name the same product. Adding that restriction would be a new business policy rather than a technical consequence of having a key.
5. “Unique” and “required” should not be treated as interchangeable words. Missing-value behaviour in a uniqueness constraint depends on the database and declaration.
6. A generated identifier is not the whole duplicate-delivery solution. Two retries may receive two different generated identifiers while still representing one attempted business action.
7. Uniqueness protects a declared representation of identity. Deciding what should count as the same thing remains a modelling and workflow responsibility.

11.2.2 PLAIN — a picture in your head#

1. Imagine labelled lockers. A rule says that no two assigned lockers may have the same complete label.
2. If a label consists of building and locker number, Building A locker 17 and Building B locker 17 can both exist. The number alone is incomplete.
3. Blank labels raise a separate question. Are blanks prohibited, allowed many times, or allowed only once? The word “unique” alone is not enough to infer a portable answer.
4. Where the comparison breaks: database equality can depend on types and text-comparison rules, and generated keys are not physical locker tags. A visually similar spelling is not automatically the same database value under every configuration.
5. The analogy also cannot detect whether somebody created two different labels for one real-world request. That problem needs the application’s identity and retry policy.
6. Keep complete-key uniqueness separate from real-world duplicate detection, just as Chapter 5 separated repeated delivery from a genuinely new event.

11.2.3 PLAIN — a worked example#

1. Start from the canonical line identity O-1042, line 1. A second insert with that same pair is rejected by the key even if it tries to supply a different quantity.
2. O-1043, line 1 is accepted because the order component differs. O-1042, line 2 is accepted because the line component differs.
3. The earlier repeated-product exercise can contain two P-NOTE lines with different line numbers. The key protects line identity rather than product uniqueness within an order.
4. A separate table demonstrates missing references:

CREATE TABLE refs (
    reference TEXT UNIQUE
) STRICT;

5. The lab inserts NULL, NULL and the present reference R-1. All three inserts succeed. A second R-1 is rejected. The result contains two missing references, not evidence that those missing references identify the same thing.
6. That observed outcome is SQLite’s behaviour for this definition. PostgreSQL 17 provides an explicit NULLS NOT DISTINCT option when a different uniqueness treatment is desired; that syntax is not used in the SQLite lab. [S61] [S02]
7. Do not “repair” this by replacing every missing reference with the text UNKNOWN. That would invent a present value and then make all unknown cases collide with it.

11.2.4 PLAIN — what is really happening inside#

1. The engine compares the constrained values against its maintained key structure. It can therefore reject a competing write that violates the same declared uniqueness rule.
2. A separate application query saying “does this identifier exist?” is not a replacement. Another writer could act after that query and before the insert unless the complete operation has appropriate protection.
3. The key check removes one class of race: accepting duplicate constrained identities. It does not automatically coordinate every other effect associated with a request.
4. A key collision can mean different things. It might be an exact retry, conflicting content under the same identity or an actual identifier-allocation error. The database’s rejection does not choose the application’s response policy.
5. The earlier file-import and event-consumer exercises already use deliberately different duplicate policies for different boundaries. Do not collapse those into one generic “ignore duplicates” instruction.
6. When a write fails, compare the right evidence under the chosen operation’s policy. Automatically overwriting the existing record would change a uniqueness rejection into an unreviewed mutation.

11.2.5 TECHNICAL — the engineer’s version#

1. Candidate keys describe possible unique identifiers; a primary key designates one for the table. Additional unique constraints can enforce other candidate identifiers where the business contract actually requires them.
2. In this lab, explicit NOT NULL declarations accompany the text and composite keys. SQLite has historical primary-key nullability exceptions outside some table forms, so do not generalise from this STRICT schema to every legacy SQLite table. [S61] [S60]
3. The constrained columns define scope. PRIMARY KEY (order_id, line_no) is not equivalent to separate uniqueness constraints on each component. The latter would wrongly allow only one line per order and only one use of each line number across the table.
4. PostgreSQL’s primary and unique constraints are associated with supporting unique indexes. The broader indexing mechanisms are treated later; at this stage the important distinction is between the declared invariant and an application’s preliminary existence query. [S74]
5. Input normalization must be chosen before a uniqueness promise is interpreted. Trimming or case-folding an identifier after acceptance can merge previously distinct labels. Our key examples use their exact stated strings and do not invent a general normalization policy.
6. For retries, a unique request key can be one part of an idempotency design, but the design must also define scope, payload agreement, retained result and transactional effects. None is established merely by adding an auto-generated row identifier.
7. SQLite’s INSERT OR REPLACE is not a neutral spelling of “update this existing record”. For a relevant key conflict, its replacement behaviour can remove the conflicting row. Do not use it as an unexplained repair for failed uniqueness in these exercises. [S76]
8. The new tests retain failed duplicate-key inserts as failures and demonstrate valid alternate line identities separately. That preserves the distinction between a rejected conflict and an accepted new business record.

11.2.6 WORDS — remember these#

1. Unique constraint: a prohibition on repeated constrained values — an invariant over one column or a composite value under the engine’s comparison and NULL rules.

2. Candidate key: a possible way to identify each row uniquely — a minimal identifying attribute set under the model’s constraints.

3. Primary key: the chosen required identifier for a table — the designated key used as a principal row identity in the current schema.

4. Key collision: two attempted records using the same constrained identity — a uniqueness conflict whose business interpretation requires the surrounding policy.

5. Existence precheck: asking whether a record is present before attempting a write — a read that does not by itself prevent a concurrent conflicting insert.

11.3.1 PLAIN — in simple words#

1. A foreign key connects a stored reference to a record that must exist. A line naming P-NOTE should refer to an existing product record rather than an arbitrary spelling that merely looks plausible.
2. The referencing record is often called the child. The record it refers to is the parent. These names describe the direction of the reference, not human family relationships.
3. If the child reference is required, it needs a presence rule as well as a foreign key. An optional reference may be absent without requiring a made-up parent.
4. Removing a referenced parent raises a policy question. Should deletion be blocked, dependent children be deleted, or the link become absent? The right answer depends on what the records mean.
5. The existing shop uses restrictive deletion for the referenced products and orders. It does not silently delete agreed lines because somebody removes a catalogue entry.
6. A foreign key proves a structural relationship under the active enforcement conditions. It does not prove that a person is the rightful customer, that a product is currently sellable or that a payment was authorized.
7. A reference can contain several components. Every component’s meaning and missingness must be specified, or a half-filled reference can evade the assumption a reader thought the diagram expressed.

11.3.2 PLAIN — a picture in your head#

1. Imagine slips that refer to cards in a catalogue. Before accepting a required slip, a clerk checks that the named catalogue card exists.
2. Before removing a card, the clerk checks what still points to it. Depending on policy, the removal may be refused or coordinated with changes to the slips.
3. An optional blank reference means no card is linked. It should not cause the clerk to create a card named “nobody” and pretend a real link exists.
4. If a reference is “branch plus local number”, a slip showing only the branch is incomplete. The desk needs a clear rule for that half-filled case.
5. Where the comparison breaks: SQL engines have specified null-matching, deletion-action and timing semantics. A drawing of an arrow contains none of those details unless the actual declaration supplies them.
6. The clerk also does not authenticate the real-world story behind the card. Existence and authority are different claims.

11.3.3 PLAIN — a worked example#

1. Reuse Chapter 5’s scoped receipt identities as a new relationship demonstration. The parent table contains (BR-A, 17) and (BR-B, 17). No amounts or order links are inferred from those labels.
2. An optional child reference uses two columns: branch and local number. The intended policy is “both absent, or both supplied and naming a parent”.
3. A plain nullable composite foreign key is not sufficient for that policy in SQLite. A row with branch BR-MISSING and a NULL local number is accepted in the deliberately loose table.
4. The stronger example adds an all-or-none check:

CHECK ((branch IS NULL) = (local_no IS NULL)),
FOREIGN KEY (branch, local_no)
    REFERENCES receipts(branch, local_no)

5. The check compares two known yes-or-no answers about absence. Both absent gives true; both present gives true; only one absent gives false.

6. If the relationship were required, both columns would instead need not-null rules as well. Allowing a completely absent optional link is a deliberate policy, not an accident.
7. None of these tests proves that a linked receipt belongs to an authorized actor. The identifiers demonstrate referential structure only.

11.3.4 PLAIN — what is really happening inside#

1. The engine uses the child values to look for an eligible parent key. The declaration must reference a suitable identifying set of parent columns, not arbitrary unrelated values.
2. Composite matching is a match on the complete tuple. BR-A’s receipt 17 and BR-B’s receipt 17 remain distinguishable even though the numeric component is equal.
3. In the nullable SQLite case, any missing child component avoids the ordinary parent-match requirement. That is why the separate all-or-none check is needed for this exercise’s intended optional link. [S59]
4. Deletion actions control consequences when an existing parent is removed. Restriction blocks the conflicting removal; cascade propagates a deletion to children; setting a reference to NULL still has to respect the other declared rules.
5. The existing child foreign key does not require every parent to have children. A catalogue product can be unused, and a parent order can be empty in the current model.
6. SQLite foreign-key enforcement is a connection setting that the helper enables and verifies before the exercises. A declaration alone is not a sufficient description of an uninspected connection’s behaviour. [S59]
7. The default helper-created practice database keeps enforcement enabled. A deliberately unprotected legacy-data demonstration later uses a different newly created in-memory database and is clearly marked as a counterexample.

11.3.5 TECHNICAL — the engineer’s version#

1. A foreign key combines a child column list, a parent target and referential actions. It is separate from application authorization, business-state eligibility and parent participation minima.
2. The lab’s order_lines has non-null order_id and product_id, referencing orders and products with ON DELETE RESTRICT. The optional orders.customer_id has different presence semantics.
3. PostgreSQL supports MATCH FULL for an all-or-none nullable composite foreign key. SQLite does not enforce the MATCH clause in that way, so the executed example uses the explicit row CHECK rather than copying unsupported semantics. [S74] [S59]
4. Test a missing parent, an absent optional reference and a half-filled composite reference independently. A successful test of one does not establish the other two.
5. A child-side index is a performance consideration rather than the definition of referential correctness. PostgreSQL does not automatically create every useful child-side index merely because a foreign key exists; later chapters study those access costs. [S74]
6. Avoid using cascade as a default housekeeping shortcut. Deleting a product and automatically deleting historical agreed lines would discard evidence the current model intends to preserve. Whether any cascade is appropriate must follow the represented objects’ lifecycle.
7. Check actual connection setup with PRAGMA foreign_keys, and inspect existing data with PRAGMA foreign_key_check when validating the specific SQLite relationship state. Enabling a setting and checking old data are different operations. [S75]
8. These exercises do not change the owner’s database, access a server or disable checks in an existing file. Every deliberately defective reference is confined to a new disposable in-memory database.

11.3.6 WORDS — remember these#

1. Foreign key: a stored link required to match an eligible parent — a referential constraint on child values with defined missingness, actions and checking time.

2. Composite reference: a link made from more than one value — a tuple of child columns identifying a parent within the corresponding scope.

3. Referential action: what happens to a relationship when a parent changes — a declared response such as restriction, cascading deletion or setting a reference to NULL.

4. Half-specified reference: only part of a multi-component link is supplied — an incomplete child tuple that needs an explicit acceptance policy.

5. Orphan row: a child with no required matching parent — an existing record violating the intended referential relationship.

11.4.1 PLAIN — in simple words#

1. An invariant is something that must remain true after the operations covered by a rule. Some invariants describe one row; others describe a collection of rows together.
2. “Every allocation is between one and ten” is a row rule. “The sum of all allocations never exceeds capacity ten” is a collection rule. The first does not imply the second.
3. Every individual entry can look acceptable while their combined result is unacceptable. This is why a collection of green validation marks can still hide an over-allocation.
4. Uniqueness and foreign keys already enforce particular relationships across rows. But they do not express every possible sum, minimum count, time overlap or workflow rule.
5. A complete solution must coordinate the operation that changes the collection. Reading the current total and later inserting a new row without adequate coordination can leave a race between writers.
6. Do not claim that a transaction automatically chooses the right coordination policy. The operation, engine isolation and write conditions must match the invariant being protected.
7. This chapter demonstrates the gap using a small counterexample. It does not claim to implement a production reservation service or explain the earlier shop’s unresolved stock discrepancy.

11.4.2 PLAIN — a picture in your head#

1. Imagine a room with ten chairs. Each group booking must request between one and ten chairs.
2. A booking for seven passes that individual rule. A booking for six also passes. Together they need thirteen chairs.
3. A clerk checking only each form’s range has not checked the room’s total. A second clerk may make the same mistake even more quickly.
4. To protect the total, the booking process must make decisions against a shared, appropriately coordinated capacity state rather than accept each form in isolation.
5. Where the comparison breaks: different database designs coordinate writes differently, and not every invariant should be represented by a mutable chair counter. The analogy demonstrates arithmetic insufficiency, not one universal implementation.
6. It also does not settle cancellation, expiration, no-shows or authorization. Those are additional workflow requirements, not consequences of the number ten.

11.4.3 PLAIN — a worked example#

1. The lab creates a separate capacity record CAP-DEMO with total 10. Its allocation rows each require a present integer quantity between 1 and 10.
2. Allocation A-1 requests 7. Its row check passes. Allocation A-2 requests 6. Its row check also passes.
3. Both references point to the existing capacity record, so the foreign-key checks pass as well.

4. No simultaneous execution is needed to demonstrate this first defect. The two inserts can happen one after the other and still produce a total of 13.
5. That matters diagnostically. Before blaming a complicated race, first check whether the sequential operation even enforces the intended rule.
6. A later concurrency design must also ensure that two operations cannot each make decisions against an inadequately coordinated view. Correctness under one sequential fixture is necessary evidence, not a complete concurrency proof.
7. CAP-DEMO is an isolated arithmetic example. Its excess of three has no connection to the older expected-stock-10, observed-stock-9 case; the cause of that older observation remains unstated.

Figure 11 — Different rules protect different properties. Row checks, unique identities and parent references can all pass while the combined allocation of 13 exceeds capacity 10.

11.4.4 PLAIN — what is really happening inside#

1. The declared check is evaluated against each proposed allocation row. It sees a permitted quantity, not an automatically maintained sum over every related allocation.
2. The foreign key answers “does CAP-DEMO exist?”. It does not interpret the parent’s total column as an allocation ceiling.
3. A report can calculate the sum and reveal the violation after it exists. Detection is useful, but it is not prevention.
4. An application-only precheck can also calculate a sum before a write. Its usefulness depends on whether the subsequent write is coordinated with every relevant competing change.
5. Possible designs include a guarded update of authoritative capacity state, an appropriately locked transaction or a different representation that makes the desired invariant directly enforceable. The details and trade-offs belong to the later transaction and concurrency chapters.
6. A trigger is code invoked by a database event. Naming a trigger as a solution does not show that its read/write sequence, isolation assumptions and error handling actually protect the invariant.
7. The immediate design task is to state the complete invariant and identify which operations can change it. Only then can the mechanism and tests be judged against the right problem.

11.4.5 TECHNICAL — the engineer’s version#

1. Write the two predicates separately. The row predicate is 1 <= q_i <= 10 for each allocation i. The capacity invariant is sum(q_i for active allocations) <= capacity. “Active” itself requires a lifecycle definition in a real system; the toy example treats its two rows as the represented allocations.
2. The first predicate allows the assignment q_1 = 7, q_2 = 6. The second rejects it when capacity is 10. That explicit assignment disproves the implication from row bounds to total safety.
3. PostgreSQL’s CHECK mechanism is not a supported way to query arbitrary other table rows and continuously maintain such a condition. Its constraints documentation directs attention to appropriate mechanisms for cross-row relationships. [S02]
4. A separate stored allocated_total would create another invariant: the counter must equal the represented allocations. Protecting the counter’s upper bound while allowing it to drift from its underlying records is not a complete solution.
5. For any proposed implementation, enumerate every mutation: add allocation, change quantity, cancel allocation, expire it, reduce capacity and repair historical data. An invariant is only maintained if relevant write paths preserve it under their declared ordering and failure rules.
6. PostgreSQL isolation levels and retry behaviour are product-specific. The book’s future concurrent examples must specify those conditions rather than importing SQLite’s single-writer observations as a cross-engine guarantee. [S66]
7. The current test intentionally asserts that the row checks pass and the global rule fails. That is a regression test for the explanation’s counterexample, not a passing safety test for an allocation service.
8. The same reasoning applies to minimum participation. A child-to-parent foreign key alone does not express “every final order has a line”. A finalisation boundary and its mechanism must be designed before testing that stronger claim.

11.4.6 WORDS — remember these#

1. Invariant: a condition that must keep holding — a predicate preserved across the operations within its specified scope.

2. Cross-row invariant: a rule about several records together — a condition involving relationships, aggregates or coordinated state beyond one proposed row.

3. Detection versus prevention: finding a violation versus stopping it from being committed — distinct responsibilities requiring different evidence.

4. Authoritative counter: a stored total used to make decisions — state whose agreement with the underlying events or allocations needs its own maintained invariant.

5. Coordination boundary: the operations that must be ordered or made indivisible together — the scope within which an invariant’s read, decision and write are protected.

11.5.1 PLAIN — in simple words#

1. An operation can consist of several statements. Sometimes an intermediate statement temporarily leaves a relationship incomplete, while the completed transaction restores it before becoming accepted work.
2. Immediate checking rejects a violation at the relevant earlier boundary. Deferred checking allows a supported rule to be checked later, commonly at transaction commit.
3. Deferring a rule is not disabling it. The completed transaction must still satisfy the deferred requirement or commit fails.
4. Consider adding a child and its parent in one transaction. A deferred foreign key can allow the child insert first, provided the required parent exists by successful commit.
5. A statement returning successfully is therefore not always evidence that the whole transaction can commit. The remaining deferred check may still reject the batch.
6. Failure handling matters too. In the SQLite demonstration, a failed commit caused by the missing deferred parent leaves the transaction open. The application must decide how to resolve or abandon it.
7. Not every constraint supports deferral, and referential actions can have different timing. Learn the exact engine’s rules instead of assuming that one keyword postpones everything.

11.5.2 PLAIN — a picture in your head#

1. Imagine assembling an application packet. During assembly, one sheet can refer to another sheet that has not yet been placed in the folder.
2. The submission desk checks the complete folder before accepting it. A temporary gap while assembling is allowed; a gap in the submitted packet is not.
3. Immediate checking resembles checking every sheet as soon as it enters the folder. Deferred checking resembles waiting for the declared submission boundary for the supported relationship.
4. If submission fails, the folder may still be on your desk rather than having vanished. You must either add the missing material under the allowed process or discard the unfinished packet.
5. Where the comparison breaks: transactions have engine-defined visibility, locking and error states. A paper folder does not model those details or establish that an application is entitled to repair every failure automatically.
6. The analogy explains timing, not weaker correctness. The final acceptance condition is still required.

11.5.3 PLAIN — a worked example#

1. The lab creates a new parent/child pair with a deferred foreign key. These are isolated teaching tables, not the canonical shop tables.

CREATE TABLE batch_parent (
    id TEXT NOT NULL PRIMARY KEY
) STRICT;

CREATE TABLE batch_child (
    id TEXT NOT NULL PRIMARY KEY,
    parent_id TEXT NOT NULL REFERENCES batch_parent(id)
        DEFERRABLE INITIALLY DEFERRED
) STRICT;

2. Start a transaction. Insert CHILD-1 referencing PARENT-1 while PARENT-1 is absent. The insert returns successfully in this demonstration.
3. Try to commit without adding the parent. Commit fails with a foreign-key constraint error. The lab observes that the connection is still in a transaction.
4. Compare two separately executed outcomes:

5. The repair branch is allowed because this particular demonstration deliberately supplies the missing parent. It is not a rule to manufacture parent records in a real application merely to silence an error.
6. A second timing test uses ON DELETE RESTRICT on a declared deferred reference. Deleting the referenced parent is rejected during the delete operation, before an attempted commit.
7. That second result prevents an overbroad conclusion: “this foreign key is deferred” does not mean every associated referential action is postponed until commit. [S59]

11.5.4 PLAIN — what is really happening inside#

1. The engine tracks whether the transaction’s supported deferred relationships are satisfied. Intermediate statement success and final transaction acceptance are distinct stages.
2. The child-first insert leaves a pending relationship problem. Inserting the matching parent within the same transaction removes it; leaving the parent absent causes the commit check to fail.
3. In the observed SQLite failure, the transaction remains available for a valid repair or explicit rollback. Ignoring the exception could leave locks or uncommitted work around longer than intended.
4. A rollback abandons the changes within the transaction. It does not mean an earlier committed parent or unrelated database was erased.
5. Restrictive deletion answers a different immediate question: a referenced parent is not to be removed by that operation. Its action timing must be understood separately from the ordinary deferred relationship check.
6. The application needs clear ownership of the transaction. A helper should not silently commit or roll back a transaction started by its caller unless its contract explicitly permits that behaviour.
7. The new guarded-draft helper therefore refuses to start when the caller already has an active transaction. That makes its narrow ownership rule visible rather than guessing what the surrounding application intended.

11.5.5 TECHNICAL — the engineer’s version#

1. The executed child example declares DEFERRABLE INITIALLY DEFERRED on a SQLite foreign key and runs inside an explicit transaction. With no surrounding explicit transaction, a single statement’s automatic transaction boundary would not provide the same multi-statement construction window. [S59]
2. The lab records statement return, first commit failure, Connection.in_transaction, final child count and final transaction state. Those are distinct observations; a single “error raised” assertion would not establish the complete recovery behaviour.
3. SQLite documents that a failed commit due to deferred foreign-key violations leaves the transaction open. Its RESTRICT action is processed promptly even when the corresponding foreign key is deferred. The two isolated tests exercise those specific documented conditions. [S59]
4. PostgreSQL 17 provides SET CONSTRAINTS to change checking mode for eligible deferrable constraints in the current transaction. Switching from deferred to immediate can force outstanding work to be checked then. This is a documented comparison, not an operation run by our SQLite helper. [S73]
5. Do not assume that not-null and CHECK conditions can all be deferred with that command. PostgreSQL documents the eligible classes; the CREATE TABLE declaration must also support the intended timing. [S73] [S74]
6. Deferred enforcement can simplify coordinated insertion of related data, but it changes where an error can surface. Callers must handle failure at commit as well as at execute time.
7. A correct retry or repair policy depends on the failure cause and transaction state. Reissuing one statement blindly can be inappropriate after other failures; read the exact engine/driver documentation and retain the failed operation’s context.
8. No power interruption, multi-host transaction or PostgreSQL runtime was tested here. These results establish selected SQLite transaction-boundary behaviour in the disclosed local environment, not a general durability or cross-engine guarantee.

11.5.6 WORDS — remember these#

1. Immediate constraint: a rule checked at its ordinary early boundary — an invariant enforced without the supported transaction-level postponement.

2. Deferred constraint: a rule whose check is postponed to a declared later boundary — an eligible constraint allowed to be temporarily unsatisfied within a transaction but required by successful final checking.

3. Commit-time failure: rejection when trying to complete a transaction — an error arising at the commit boundary rather than necessarily during an earlier statement.

4. Transaction ownership: responsibility for starting and ending a transaction — a contract defining which component may commit, roll back or leave the connection active.

5. Repair branch: a deliberate resolution of incomplete transactional work — an explicitly permitted continuation that restores the failed condition before a later commit.

11.6.1 PLAIN — in simple words#

1. An error is information about an operation, not a universal description of everything the database has done. Ask what failed, what remains and whether a transaction is still open.
2. A failed statement can be undone while earlier statements in the same transaction remain pending. A complete rollback is a different event.
3. A key conflict, a missing parent and a locked database have different causes. Showing users the same “something went wrong” message hides useful distinctions; guessing a cause from incomplete evidence is no better.
4. A constraint change also has a before and after. Adding a rule raises two questions: will future operations obey it, and do existing records already satisfy it?
5. Enabling foreign-key enforcement on a connection is not a retroactive cleanup operation. Old orphan rows do not magically gain parents or disappear.
6. A migration is a controlled change to the database structure or representation. It needs a plan for existing data, compatible readers and writers, verification and failure handling.
7. The exercises here illustrate error states and validation gaps in new in-memory databases. They do not run a migration on a real shop database or authorize changes to any existing installation.

11.6.2 PLAIN — a picture in your head#

1. Imagine writing three entries in pencil before signing the page. The second entry is rejected and erased, but the first entry may still remain unsigned on the page.
2. “The second entry failed” does not tell you whether the whole page was discarded. You need to inspect the actual procedure.
3. Now imagine introducing a new rule that every old folder must have a matching index card. Announcing the rule today does not manufacture index cards for yesterday’s incomplete folders.
4. Where the comparison breaks: database engines define precisely which statement effects and transaction effects survive each failure class. The pencil analogy cannot replace checking those rules and actual connection state.
5. It also says nothing about safe restoration after an irreversible external action. A transaction rollback in this lab does not retract an email, refund a payment or reverse a file sent elsewhere.
6. Use the picture to ask the right questions: what is pending, what is committed, what is invalid, and which recovery action is actually allowed?

11.6.3 PLAIN — a worked example#

1. The statement-failure demonstration starts an explicit SQLite transaction in a new table with a primary key.
2. Its first insert adds identifier 1. A later single statement tries to insert identifiers 2 and 1. The repeated 1 causes a primary-key failure under the default ABORT behaviour.
3. The lab then inspects the same connection. Identifier 1 from the earlier successful statement is still present inside the transaction; identifier 2 from the failed statement is not.
4. The transaction is still open. An explicit rollback removes the earlier uncommitted identifier 1 as well. The final table is empty.
5. A separate deliberately unprotected database is created only for a legacy-data counterexample. It stores one orphan child while enforcement is off, then enables enforcement before inspection.

6. The specific relationship check finds the problem that the general integrity check does not cover. SQLite documents their different scopes. [S75]
7. Neither demonstration is an instruction to disable checks in an existing database. Both create their own disposable in-memory structures, and no user file is opened.

11.6.4 PLAIN — what is really happening inside#

1. A statement has an execution boundary; a transaction has a larger acceptance boundary. The engine’s conflict handling determines which effects are undone for a particular error.
2. In the tested default ABORT case, the failing statement’s changes are backed out, while earlier statements in the active transaction remain pending. Explicit rollback abandons that transaction’s earlier work too. [S76]
3. The application must not report a successful complete operation merely because the first statement succeeded. It must observe the required final boundary and handle the actual error state.
4. A migration adds another kind of boundary: the database and its consumers move from an old contract to a new contract. Existing rows can conflict with the new rule even when future writes are well validated.
5. Inspecting existing records produces evidence, but an inspection result can go stale while writers continue changing the database. A real migration plan must coordinate validation and enforcement rather than assume a one-time query remains true forever.
6. Any repair needs a meaning-preserving policy. Automatically deleting orphan rows or inventing missing parents may silence structural errors while destroying evidence or creating false claims.
7. For the book’s fictional examples, the correct action is to report the deliberate violation and discard the disposable exercise database. There is no production repair decision to infer.

11.6.5 TECHNICAL — the engineer’s version#

1. The statement-failure test records the SQLite extended error name SQLITE_CONSTRAINT_PRIMARYKEY, the remaining rows and transaction state. Programmatic error codes or classes are preferable to treating the exact English wording of one driver message as a permanent interface. [S77] [S86]
2. Do not generalise this one ABORT case to every SQLite error or conflict algorithm. Other failure classes and policies have different rollback effects. The lab intentionally uses ordinary statements and reports the tested scope. [S76]
3. For a planned constraint change, prepare an inventory of old data violations, the proposed resolution policy, the final declaration and the verification query. Keep the evidence before and after the change separate.
4. PostgreSQL’s ALTER TABLE facilities include distinct ways of adding and validating supported constraints. Which operations scan existing rows and which locks or compatibility effects arise depends on the exact operation. Those details require a version-specific migration plan rather than a generic “run ALTER TABLE” recipe. [S78]
5. A portable design discussion does not imply portable syntax. SQLite and PostgreSQL expose different table-change capabilities. None of the PostgreSQL migration examples is executed in the companion labs.
6. Test both desired rejections and preserved valid cases after a proposed rule change. A rule that blocks all writes will reject bad data while also destroying the intended workflow; rejection counts alone do not establish correctness.
7. Keep validation queries and the data snapshot they examined identifiable. A report saying “zero violations” without its database, schema version, timing and concurrent-write assumptions is incomplete evidence.
8. The current lab provides no backup orchestration, online migration, multi-client deployment or production rollback plan. Its contribution is to make the error and validation boundaries observable before later operational chapters build on them.

11.6.6 WORDS — remember these#

1. Statement rollback: undoing effects of one failed statement — a failure response distinct from abandoning all earlier work in the active transaction.

2. Transaction rollback: abandoning a transaction’s pending changes — an explicit or engine-triggered reversal within that transaction’s defined scope.

3. Constraint validation: checking records against a declared rule — an operation whose data scope and timing must be distinguished from future enforcement.

4. Schema migration: a controlled change to database structure or representation — a transition requiring data compatibility, verification and a defined failure disposition.

5. Extended error code: a machine-readable refinement of a failure category — an implementation-specific identifier that distinguishes more precise error causes.