WEB / WEB-18.01

Relational data and relationships

Model application state through tables, rows, keys, and relationships. In public books, see joins and referential constraints serve different purposes.

CORE · RED · BLUE · ARTIFACT · PRACTICE

Read every scenario without motion

Author join

Catalog state
The public synthetic relational catalog and selected FK=1 connection are ready; no query result has been produced.
Application
No query
SQLite driver
Connection FK=1
Relational tables
2 books; 3 links
Query result
No result
Choose operation
The application selects a fixed SELECT joining books.author_id to authors.id and ordering by b.id. This author-join statement has no placeholder or bound parameter; table and column names are fixed in application code.
Application
Join requested
SQLite driver
Connection FK=1
Relational tables
2 books; 3 links
Query result
No result
Driver binding
The driver passes the fixed author-join statement to the in-process SQLite connection. FK=1 is separate connection configuration; executing a SELECT is not a write-constraint test.
Application
Join requested
SQLite driver
Local SQLite operation executing
Relational tables
2 books; 3 links
Query result
No result
Relational decision
The SELECT matches author_id=1 in each book to authors.id=1 and obtains Duru for Tea book and Coffee book. ORDER BY b.id places book 10 before book 11 in the result.
Application
Join requested
SQLite driver
Local SQLite operation executing
Relational tables
Joined by author_id
Query result
No result
Result shape
The cursor result contains two ordered rows: Tea book/Duru and Coffee book/Duru. This named read result establishes neither an authenticated author identity nor application resource permission.
Application
Join requested
SQLite driver
Local SQLite operation executing
Relational tables
Joined by author_id
Query result
Tea/Duru; Coffee/Duru
Application result
The application uses the selected result; closing this in-memory connection offers no durable disk-catalog guarantee.
Application
Public results ready
SQLite driver
Local operation completed; result returned
Relational tables
Joined by author_id
Query result
Tea/Duru; Coffee/Duru

Missing parent

Catalog state
The public synthetic relational catalog and selected FK=1 connection are ready; no query result has been produced.
Application
No query
SQLite driver
Connection FK=1
Relational tables
2 books; 3 links
Query result
No result
Choose operation
The application selects author_id=99 as the candidate value for a new book row. The authors record contains only id=1; insert acceptance depends on this connection’s immediate foreign-key constraint.
Application
Orphan row candidate
SQLite driver
Connection FK=1
Relational tables
2 books; 3 links
Query result
No result
Driver binding
The driver supplies the insert operation and author_id=99 data locally to SQLite. No row has been added yet; FK=1 and the NOT NULL child-key condition are applied during insertion.
Application
Orphan row candidate
SQLite driver
Local SQLite operation executing
Relational tables
2 books; 3 links
Query result
No result
Relational decision
The immediate FOREIGN KEY constraint denies insertion because authors.id=99 does not exist. The books count remains 2; a successful author join is not the cause or evidence of this denial.
Application
Orphan row candidate
SQLite driver
Local SQLite operation executing
Relational tables
Missing parent 99; rejected
Query result
No result
Result shape
The driver result carries IntegrityError with books=2. The candidate was not stored; this result is consumed by the application at the next local step and makes no decision about actor identity or intent.
Application
Orphan row candidate
SQLite driver
Local SQLite operation executing
Relational tables
Missing parent 99; rejected
Query result
IntegrityError; books=2
Application result
The application uses the selected result; closing this in-memory connection offers no durable disk-catalog guarantee.
Application
Candidate not stored
SQLite driver
Local operation completed; result returned
Relational tables
Missing parent 99; rejected
Query result
IntegrityError; books=2

Book tags

Catalog state
The public synthetic relational catalog and selected FK=1 connection are ready; no query result has been produced.
Application
No query
SQLite driver
Connection FK=1
Relational tables
2 books; 3 links
Query result
No result
Choose operation
The application selects a read of three book_tags pairs: [10,1], [10,2], [11,1]. Tea book has two distinct tags; this operation inserts neither a book nor an association.
Application
Tags query requested
SQLite driver
Connection FK=1
Relational tables
2 books; 3 links
Query result
No result
Driver binding
The driver passes a fixed association read to the in-process SQLite connection. Table/column selection is not derived from data; tag relationships are read without a write mutation.
Application
Tags query requested
SQLite driver
Local SQLite operation executing
Relational tables
2 books; 3 links
Query result
No result
Relational decision
Books and tags match through the book_id/tag_id pairs in book_tags. There are three associations; book_id=10 matches two distinct tags, so one book can produce two result rows.
Application
Tags query requested
SQLite driver
Local SQLite operation executing
Relational tables
Join 3 association rows
Query result
No result
Result shape
Tea book’s tag result is web and notes; distinct-book count remains 2 while association count is 3. The composite key limits duplicate book/tag pairs, not the two different tags on book_id=10.
Application
Tags query requested
SQLite driver
Local SQLite operation executing
Relational tables
Join 3 association rows
Query result
Tea: web + notes
Application result
The application uses the selected result; closing this in-memory connection offers no durable disk-catalog guarantee.
Application
Tea tags: web + notes; 2 books / 3 pairs
SQLite driver
Local operation completed; result returned
Relational tables
Join 3 association rows
Query result
Tea: web + notes

ACTORS · STATE · DATA FLOW

Why is a database separate from browser state?

A web application may need structured state shared across requests and users: a public book catalog, author information, or a saved note. A database owns queryable data and defined consistency boundaries. Browser preference cookies or a variable in one process do not provide the same ownership. Nor does choosing a database automatically make all storage durable: this relational SQLite example lives in a :memory: connection and makes no disk-durability claim.

In the relational model, a table is a named structure, a column an attribute, and a row one record within it. authors id/name differ from books id/title/author_id. A primary key defines unique row identity; names/titles are not automatically unique. The fixture gives Tea book and Coffee book separate IDs under one author. Requested row order needs explicit ORDER BY rather than treating a table as an inherently ordered list.

Column/type/constraint definitions form a data contract. SQLite can use column affinity; do not assume every relational engine enforces types identically. Our authors.id and books.id use INTEGER PRIMARY KEY, while name/title and author_id are NOT NULL. IDs do not implement application authorization. Finding a row or observing an integer key does not authorize its presentation; these are public synthetic catalog records.

Keys constrain relationships; joins produce results

books.author_id references authors.id through a foreign key. Multiple books under one author demonstrate one-to-many; each NOT NULL author_id connects a book to one parent in this fixture. The foreign key requires an existing parent key, not a copy of every parent attribute. A join matches books to authors and produces book title and author name in one result row. A constraint and a query are different mechanisms.

SQLite foreign-key support is explicitly enabled with PRAGMA foreign_keys=ON for this connection and observed as 1. A CREATE TABLE declaration alone does not prove enforcement. This example uses an immediate constraint: a supplied harmless insertion with author_id=99 has no parent, so it is rejected and the books row count stays unchanged. Without NOT NULL, a NULL child key could have another meaning; not every foreign key is a mandatory relationship.

Many-to-many uses a book_tags association table between books and tags, with a composite primary key over book_id/tag_id. Tea book can have two tags while a shared tag relates to two books. Three association rows connect the fixture tables; duplicate pairs are rejected by the key constraint. Cardinality comes from uniqueness, nullability, and association structure together, not column names alone. Delete/update policies are separate; no cascade is assumed here.

Read every scenario without motion

FK enforcement

The relationship declaration was read
books.author_id declares a FOREIGN KEY to authors(id) and is NOT NULL. Declaration alone does not show that checking is enabled on the connection.
Schema and catalog
author_id → authors.id
Relation gate
Enforcement pending
Application mapping
Row count=2
The connection condition was matched
Enforcement declares foreignKeysEnabled=1 and immediate constraint timing. These conditions belong to the selected connection, not every SQLite connection.
Schema and catalog
foreignKeysEnabled=1
Relation gate
Immediate constraint
Application mapping
Row count=2
The parent-reference outcome was read
orphanAuthorId=99 has no parent record and orphanAttemptAccepted=false. The referential-integrity gate does not accept this harmless catalog row.
Schema and catalog
Parent 99 absent
Relation gate
Insert accepted=false
Application mapping
Final count pending
The negative-mutation boundary was checked
rowCountBefore=2 and rowCountAfter=2 are equal. Denial and final state are read together; a constraint name alone cannot complete the no-write outcome.
Schema and catalog
Known FK rejection
Relation gate
Before=2; after=2
Application mapping
No new book row
A JOIN was not counted as integrity enforcement
The normal JOIN produces two title/name rows. A correct-looking SELECT result is not proof of write-time FK enforcement; it needs a separate enforcement record.
Schema and catalog
Query result separate
Relation gate
Write constraint separate
Application mapping
No implied enforcement
Referential integrity did not become permission
Author id=1 is a catalog relationship key, not an authenticated subject. A present parent grants an actor neither read nor update permission over books.
Schema and catalog
Data relation known
Relation gate
Identity separate
Application mapping
No permission grant

Join cardinality

The association table was reviewed
book_tags contains [10,1], [10,2] and [11,1]. Its composite primary key makes each book_id/tag_id pair unique, not book_id alone.
Schema and catalog
Association rows=3
Relation gate
Distinct books=2
Application mapping
Mapping pending
The two Tea book tags were separated
Tea book has web and notes tags. A raw join can show two associations without representing two independent books or a duplicate primary key.
Schema and catalog
Tea tags: web, notes
Relation gate
One book, two associations
Application mapping
Aggregate pending
The counted unit was established
associationRows=3 and distinctBooks=2 answer different questions. Treating association count as book total produces a wrong aggregate from correct data.
Schema and catalog
Pairs=3
Relation gate
Books=2
Application mapping
Counting unit explicit
The duplicate-pair outcome was read separately
duplicatePairAccepted=false describes composite uniqueness. One book appearing with two different tags does not satisfy that negative duplicate-pair condition.
Schema and catalog
Duplicate pair=false
Relation gate
Different tags allowed
Application mapping
No false duplicate finding
Presentation identity was preserved
A book view can group by book_id while retaining tags as a separate collection. This mapping decision is neither authentication nor authorization identity.
Schema and catalog
Group by book identity
Relation gate
Tags remain associations
Application mapping
Access policy separate
The query result was interpreted narrowly
The cardinality table supports only the supplied two books and three pairs. It does not establish ORM identity maps, concurrency or another schema’s behavior.
Schema and catalog
Selected cardinality known
Relation gate
Other mappings unobserved
Application mapping
No extra object inference

ASSUMPTION · BOUNDARY · CONTROL

Declaration and enforcement are different evidence

Do not stop at FOREIGN KEY in CREATE TABLE when reviewing a controlled fixture. The selected SQLite connection foreign_keys value and an expected constraint result provide evidence together. Rejecting a benign catalog row without a parent demonstrates referential integrity for that boundary, not another connection or schema. The failure condition is mistaking an application comment for an enforced constraint.

A JOIN producing title/name results does not prove a constraint protects relationship writes. Queries and integrity perform different tasks. Read row count, parent reference and expected rejection together to identify which operation left data unchanged. Query execution and enforcement of insert conditions remain separate; neither substitutes for the other.

Result multiplicity and identity boundaries

A many-to-many join can yield multiple rows for one logical book. Two tags on Tea book legitimately produce two rows; an application should not mistake them for duplicate books or independent objects. Possible effects include an incorrect aggregate or repeated presentation. Connect the result shape and association cardinality to the presentation mapping.

A primary key identifies data, not an authenticated subject or object permission. In this public catalog, author id=1 is not a login identity. Relationships must not imply access rights. The native in-memory check observes join/constraint results; it does not verify disk persistence, concurrent-reader isolation, ORM identity maps, or production permission policy.

Read every scenario without motion

Relation tuple

Parent and child records were linked
authors=[1,Duru] and both book rows carry author_id=1. Review reads parent key and child references within one schema context.
Schema/query observation
Author: 1 / Duru
Connection evidence review
Books: 10, 11
Evidence conclusion
Join review pending
The JOIN predicate and order were reviewed
join.sql matches a.id=b.author_id and orders by b.id. Result order comes from this explicit ORDER BY, not assumed physical table order.
Schema/query observation
Predicate: author reference
Connection evidence review
Order: b.id
Evidence conclusion
Two matched rows
Expected result rows were matched
Tea book/Duru and Coffee book/Duru show relationship reading. These rows do not replace foreign-key enforcement fields.
Schema/query observation
Tea book / Duru
Connection evidence review
Coffee book / Duru
Evidence conclusion
Read result known
The schema-enforcement tuple was added
Foreign-key declaration, pragma value, orphan rejection and final row count are all needed. Linking this tuple with the query result combines different evidence.
Schema/query observation
Declaration + pragma
Connection evidence review
Rejection + row count
Evidence conclusion
Evidence fields linked
Memory lifetime remained explicit
databaseLifetime is :memory: connection only. Closing the connection does not establish persistent disk storage of this ephemeral catalog.
Schema/query observation
Connection-owned data
Connection evidence review
Disk persistence unobserved
Evidence conclusion
Lifetime bounded
The review conclusion was bound to the operation
Known JOIN and constraint results belong to the selected schema/connection. More indexes or rows cannot turn them into resource-permission evidence.
Schema/query observation
Selected read/constraint known
Connection evidence review
Permission not established
Evidence conclusion
No broader database claim

Evidence missing

The relation-selector condition was read
The selector counts relation observations with parentExists=false and insertAccepted=true. These authored fields are not assumed to be product database log fields.
Schema/query observation
Parent absent + insert accepted
Connection evidence review
Selector review pending
Evidence conclusion
Evidence scope bounded
The normal negative observation was reviewed
The normal XML has parentExists=false and insertAccepted=false with expected=0. Zero shows that this record does not match the violation condition.
Schema/query observation
Normal expected=0
Connection evidence review
Rejected insert observed
Evidence conclusion
No matched violation
The positive comparison fixture was separated
positiveFixtureXml has parentExists=false and insertAccepted=true with positiveExpected=1. It checks selector logic, not an actual accepted-insert record.
Schema/query observation
Comparison expected=1
Connection evidence review
Inconsistent fixture only
Evidence conclusion
No incident inference
Benign cardinality did not become a false positive
Two tag associations for Tea book do not mean a missing parent. Multiple JOIN rows alone satisfy neither of the selector’s two conditions.
Schema/query observation
Two tags benign
Connection evidence review
Relation condition distinct
Evidence conclusion
No cardinality alarm
A missing observation remained unknown
When an event or required field is missing, count=0 is not clean-enforcement evidence. The conclusion remains incomplete without rejection and state results for the same connection.
Schema/query observation
Coverage may be missing
Connection evidence review
Enforcement UNKNOWN
Evidence conclusion
No clean conclusion
The need for additional evidence was recorded
Observations are needed for schema, connection pragma, attempted relation and final row count. A selector count cannot replace the presence of those bindings.
Schema/query observation
Connection evidence required
Connection evidence review
Final state required
Evidence conclusion
Observation gap retained

IMPLEMENTATION · SECURITY CONTROL · VERIFICATION

Connect the schema and result ledger

The artifact holds authors=[1,Duru] and books=[10,Tea book,1]/[11,Coffee book,1]. ORDER BY books.id produces two title/name join rows. book_tags has three unique pairs; two tags on Tea book mean grouped counts and raw join counts answer different questions. These tables/fields form a synthetic teaching schema, not product data or real users.

The enforcement record has foreignKeysEnabled=1, orphanAttemptAccepted=false, and rowCountBefore=rowCountAfter=2. A duplicate association is also rejected. Native IntegrityError is the selected SQLite constraint outcome, not a universal exception API for every relational product. Inspect nullability, parent-key uniqueness, and timing together rather than making decisions from column names.

Bind the relationship selector to one connection

XPath count(/audit/relation[@parentExists="false" and @insertAccepted="true"]) counts authored observations that accept an insert without its parent. Expected normal FK rejection gives 0; a deliberately inconsistent positive fixture gives 1. Multiple tags on one book are benign cardinality, not this violation. With no event, zero means insufficient evidence rather than proven enforcement.

After correction, connect the declaration, connection pragma, and final rows through the same bounded fixture. More rows or an index do not turn integrity into authorization. State why the database exists, which owner keeps each state, and that the example lifetime is :memory:. Closing this connection must not be mistaken for retaining a durable catalog; that distinction belongs to storage lifecycle.

A foreign-key declaration alone is not enforcement. At SQLite connection initialization, configure and read back PRAGMA foreign_keys before beginning a transaction; each pooled connection needs that policy. Changing it inside a transaction can have no effect. Specify the required PRIMARY KEY or UNIQUE parent key, child nullability and unique association pair. If the business rule requires a parent, a nullable foreign key alone is insufficient. Verify normal joins, unchanged row count after orphan rejection and duplicate-association rejection together; missing pragma or outcome evidence leaves enforcement unknown.

NOW APPLY IT TO THE CASE

Which write or identity conditions are not proved by a JOIN result?

The public catalog has two books and three tag associations. Read results, constraint enforcement and association counts are assessed through different ledger fields.

Compare the cached case representation with its authoritative data owner. Keep stored value, revision, transaction and cache-copy completion separate.

Open the related case file ↗

READ THE EXAMPLE TOGETHER

Public-books schema and relationship ledger

Select a field and examine what it supports and where the evidence ends.

Read the raw artifact and field explanations

{
  "schema": {
    "authors": {
      "columns": [
        "id INTEGER PRIMARY KEY",
        "name TEXT NOT NULL"
      ]
    },
    "books": {
      "columns": [
        "id INTEGER PRIMARY KEY",
        "title TEXT NOT NULL",
        "author_id INTEGER NOT NULL"
      ],
      "foreignKey": "author_id REFERENCES authors(id)"
    },
    "tags": {
      "columns": [
        "id INTEGER PRIMARY KEY",
        "label TEXT NOT NULL"
      ]
    },
    "book_tags": {
      "columns": [
        "book_id INTEGER NOT NULL",
        "tag_id INTEGER NOT NULL"
      ],
      "primaryKey": [
        "book_id",
        "tag_id"
      ],
      "foreignKeys": [
        "book_id REFERENCES books(id)",
        "tag_id REFERENCES tags(id)"
      ]
    }
  },
  "records": {
    "authors": [
      [
        1,
        "Duru"
      ]
    ],
    "books": [
      [
        10,
        "Tea book",
        1
      ],
      [
        11,
        "Coffee book",
        1
      ]
    ],
    "tags": [
      [
        1,
        "web"
      ],
      [
        2,
        "notes"
      ]
    ],
    "book_tags": [
      [
        10,
        1
      ],
      [
        10,
        2
      ],
      [
        11,
        1
      ]
    ]
  },
  "join": {
    "sql": "SELECT b.title, a.name FROM books AS b JOIN authors AS a ON a.id=b.author_id ORDER BY b.id",
    "rows": [
      [
        "Tea book",
        "Duru"
      ],
      [
        "Coffee book",
        "Duru"
      ]
    ]
  },
  "manyToMany": {
    "teaTags": [
      "web",
      "notes"
    ],
    "associationRows": 3,
    "distinctBooks": 2,
    "duplicatePairAccepted": false
  },
  "enforcement": {
    "foreignKeysEnabled": 1,
    "orphanAuthorId": 99,
    "orphanAttemptAccepted": false,
    "rowCountBefore": 2,
    "rowCountAfter": 2,
    "constraintTiming": "immediate, NOT NULL child key",
    "databaseLifetime": ":memory: connection only"
  },
  "selector": {
    "language": "XPath",
    "xml": "<audit><relation parentExists=\"false\" insertAccepted=\"false\"/></audit>",
    "xpath": "count(/audit/relation[@parentExists=\"false\" and @insertAccepted=\"true\"])",
    "expected": 0,
    "positiveFixtureXml": "<audit><relation parentExists=\"false\" insertAccepted=\"true\"/></audit>",
    "positiveExpected": 1,
    "scope": "Only authored protocol-decision observations, not product log field names or a live HTTP capture.",
    "missingFieldBehavior": "Missing observation is insufficient evidence; zero matches is not proof of correct runtime behavior."
  }
}

schema: Authored SQLite keys/nullability, not a product schema.
join: Ordered read result, separate from a constraint.
enforcement: Observed FK/duplicate-pair boundaries on the selected connection.

In-memory synthetic books/tags; no identity, disk durability, or ORM-runtime proof.

Two reality checks

A successful JOIN proves foreign-key enforcement is enabled.

A query produces matches; enforcement separately depends on schema and SQLite connection configuration.

A primary key or author relationship provides object authorization.

Keys support data identity/integrity; application access requires a separate policy.

YOUR TURN

Work through this story.

Predict the outcome, find the evidence boundary, and explain it in your own words.

Foreign keys are enabled=1; a new book has author_id=99, but authors has no 99. Is the selected insert accepted?

Yes; a foreign key only describes joins.
No; row count remains 2.

Read the explained answer

No; row count remains 2. — Selected orphanAttemptAccepted=false and before/after count 2/2; the parent reference is preserved.

There are 3 association rows but 2 distinct books. Why are there not 3 book rows?

Selected lesson artifact fields; teaching example

schema.book_tags.primaryKey: ["book_id","tag_id"]
records.book_tags: [[10,1],[10,2],[11,1]]
manyToMany: {"teaTags":["web","notes"],"associationRows":3,"distinctBooks":2,"duplicatePairAccepted":false}

One book appears in more than one tag pair.
Every relation row automatically creates a new book entity.

Read the explained answer

One book appears in more than one tag pair. — Tea book has two tags; the book_tags composite key book_id/tag_id prevents a duplicate pair.

Two Duru books are listed through an authors join. Why is referential integrity neither reader permission nor power-loss durability evidence?

Read the explained answer

Foreign keys preserve parent existence and composite keys preserve relationship uniqueness. This schema grants no user permission to read a particular book. Artifact databaseLifetime is :memory: connection only; persisted restart or power-loss outcomes were not measured.

□ I did not count a relation constraint as an access grant.

□ I inferred no durability guarantee from an in-memory lifetime.

Open the application console · request / response / browser

Also open request, URL, cookie and DOM examples

Sources and scope

SQLite — Foreign Key Support1. Introduction to Foreign Key Constraints · 2. Enabling Foreign Key Support · 3. Required and Suggested Database Indexes · 4.1. Composite Foreign Key Constraints · 4.2. Deferred Foreign Key Constraints
SQLite — CREATE TABLE2. The CREATE TABLE command · 3.1. Column Data Types · 3.5. The PRIMARY KEY · 3.6. UNIQUE constraints · 3.8. NOT NULL constraints
Python 3.14 — sqlite3Tutorial · sqlite3.connect · Cursor.execute · How to use placeholders to bind values in SQL queries · Transaction control via the isolation_level attribute · How to use the connection context manager

YOUR NOTES / THIS BROWSER

Relational data and relationships

1. Core · Mechanism

Author join

Missing parent

Book tags

Why is a database separate from browser state?

Keys constrain relationships; joins produce results

2. Red · Trust & Impact

FK enforcement

Join cardinality

Declaration and enforcement are different evidence

Result multiplicity and identity boundaries

3. Blue · Secure development & defense

Relation tuple

Evidence missing

Connect the schema and result ledger

Bind the relationship selector to one connection

Which write or identity conditions are not proved by a JOIN result?

Two reality checks

Foreign keys are enabled=1; a new book has author_id=99, but authors has no 99. Is the selected insert accepted?

There are 3 association rows but 2 distinct books. Why are there not 3 book rows?

Two Duru books are listed through an authors join. Why is referential integrity neither reader permission nor power-loss durability evidence?

Sources and scope