1.1 Relational Model, Constraints, and Secure Writes

A relational database organizes data into tables and uses constraints, transactions, and recovery to maintain valid states. It can reject the duplicate keys, missing references, and partial updates that are easy to miss when editing CSV files. This lesson builds a small SQLite equipment catalog to check accepted writes, rejected writes, and rollback. The database is diligent, provided you remembered to give it the rules.

Learning Objectives

Distinguish between table, row, column, key, and constraint;
Create a set of reusable practice tables using SQLite;
Correctly execute INSERT, UPDATE, and DELETE operations;
Understand the boundaries of NULL, dynamic typing, and foreign keys;
Reduce the risk of data corruption using transactions and parameter binding.

Why Not Just Maintain CSV/JSON Files?

Flat files are suitable for data exchange, small configurations, and append-only logging. However, once you need concurrent updates from multiple users, cross-record constraints, conditional queries, or crash recovery, you're forced to manually implement many core database capabilities.

Database Management Systems typically provide:

Schema and constraints to reject invalid states;
Transactions that treat a group of changes as a single logical unit;
Concurrency control to coordinate concurrent reads and writes;
Indexes and query optimizers to avoid manually scanning every record;
Logging, checkpointing, and backup mechanisms for recovery;
Authorization, auditing, and operational interfaces.

Databases don’t automatically guarantee business correctness either. Poor schema design, missing constraints, incorrect transaction boundaries, or concatenated SQL queries can still introduce consistency and security issues.

Establish Reproducible Experiments

This chapter uses the SQLite CLI version 3.37.0 or later, starting with an empty practice database. First, verify the version:

bash

sqlite3 --version

Download from the official SQLite release page or install via your operating system's package manager. The course does not provide a single installation command that works universally across platforms.

Open a dedicated practice database:

bash

sqlite3 catalog-lab.db

Enable error-detection settings in the CLI for better troubleshooting:

sql

.bail on
.headers on
.mode box
PRAGMA foreign_keys = ON;

The lines beginning with a dot are SQLite CLI commands; do not pass them directly to a SQL driver. On every new connection, run PRAGMA foreign_keys = ON outside a transaction, then check that PRAGMA foreign_keys; returns 1. Changing this setting inside a transaction has no effect. See the SQLite foreign key documentation.

Expressing Invariants with Constraints

sql

CREATE TABLE item_types (
    item_type_id INTEGER PRIMARY KEY,
    code         TEXT NOT NULL UNIQUE,
    display_name TEXT NOT NULL
) STRICT;

CREATE TABLE catalog_items (
    item_id       INTEGER PRIMARY KEY,
    item_type_id  INTEGER NOT NULL,
    name          TEXT NOT NULL UNIQUE,
    value_cents   INTEGER NOT NULL CHECK (value_cents >= 0),
    discovered_on TEXT,
    FOREIGN KEY (item_type_id)
        REFERENCES item_types(item_type_id)
        ON UPDATE RESTRICT
        ON DELETE RESTRICT,
    CHECK (
        discovered_on IS NULL
        OR discovered_on GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
    )
) STRICT;

Constraints each take on a distinct responsibility:

PRIMARY KEY: Uniquely identifies a row;
NOT NULL: Prevents unknown or missing values;
UNIQUE: Ensures candidate values are not duplicated;
CHECK: Enforces conditions within a row;
FOREIGN KEY: Requires that referenced parent rows satisfy referential integrity.

For example, storing amounts as integers in the smallest currency unit avoids using binary floating-point arithmetic for precise financial values. Real financial systems would also define currency types, rounding rules, and accounting policies.

A CHECK constraint like discovered_on only validates the textual form of a value and does not verify that a date actually exists. SQLite lacks a standalone DATE storage class; in production applications, stronger constraints, triggers, or application-level validation are recommended, along with consistent timezone and formatting rules.

SQLite's Type Boundaries

SQLite defaults to type affinity, meaning that the same column may still store values of different storage classes depending on context. PostgreSQL and MySQL also have implicit casts, modes, and domain rules; check what the target engine actually accepts.

Both tables use STRICT, so value_cents rejects text that cannot be converted losslessly to an integer. An ordinary table with just INTEGER CHECK (value_cents >= 0) can still accept nonnumeric text. STRICT permits lossless conversion; valid dates and business states still require their own rules. See SQLite STRICT Tables.

Insert with Explicit Column Names

sql

INSERT INTO item_types (item_type_id, code, display_name)
VALUES
    (1, 'device', 'Device'),
    (2, 'spare_part', 'Spare part'),
    (3, 'consumable', 'Consumable');

INSERT INTO catalog_items (
    item_id,
    item_type_id,
    name,
    value_cents,
    discovered_on
)
VALUES
    (1, 1, 'Sensor Kit',   120000, '2024-01-15'),
    (2, 3, 'Cleaning Fluid',   8000, '2024-03-01'),
    (3, 1, 'Calibration Meter', 180000, '2024-02-20'),
    (4, 2, 'Spare Enclosure',  95000, '2024-05-10'),
    (5, 3, 'Sealant',  15000, NULL);

Explicit column names prevent schema mismatches when new columns are added, and make it clear during code reviews what each value represents.

A single multi-row INSERT is one statement. If any row in that statement violates a constraint, the overall effect of the statement depends on the conflict resolution strategy and the transaction context. Do not assume that error handling behaviors across different databases are equivalent.

UPDATE and DELETE: First Confirm the Target Set

Preview the scope within a transaction:

sql

BEGIN IMMEDIATE;

SELECT item_id, name, value_cents
FROM catalog_items
WHERE item_type_id = 1
ORDER BY item_id;

UPDATE catalog_items
SET value_cents = (value_cents * 110 + 50) / 100
WHERE item_type_id = 1;

SELECT changes() AS changed_rows;

ROLLBACK;

The initial fixture has two devices, so changed_rows should be 2; after rollback their amounts remain 120000 and 180000. This experiment uses integer arithmetic to apply a 10% adjustment with explicit rounding. ROLLBACK ensures the practice does not modify the final data. After verifying the results, the operation can be executed in a new transaction and COMMIT.

SQLite's BEGIN IMMEDIATE acquires the locks required for write transactions earlier, reducing the risk of encountering write failures mid-execution. However, it should not be mechanically substituted for all BEGIN; concurrency strategies must be chosen based on the specific workload.

Deletions should also be explicitly qualified by a predicate:

sql

BEGIN;

DELETE FROM catalog_items
WHERE item_id = 5;

SELECT changes() AS deleted_rows;
ROLLBACK;

DELETE deletes rows, while DROP TABLE removes schema objects. Both operations can produce persistent changes within a transaction. Whether they can be rolled back (and the semantics of DDL transactions) must be determined by the specific database implementation.

NULL represents unknown, not an empty string

sql

SELECT item_id, name
FROM catalog_items
WHERE discovered_on IS NULL;

The result of column = NULL is UNKNOWN, and it does not select rows like a typical true predicate would. SQL predicates have three logical outcomes: TRUE, FALSE, and UNKNOWN; only TRUE values are retained by WHERE.

NULL differs from '' and 0. Whether NULL is allowed is a modeling decision: if "no date found" is a valid state, then NULL should be permitted; if the business rule requires every item to have a date, then NOT NULL should be used instead.

Don't Concatenate User Input

The following is a wrong example:

python

query = f"SELECT item_id, name FROM catalog_items WHERE name = '{user_input}'"

It treats data as SQL syntax, leading to SQL injection and quoting errors. Use driver parameter binding instead:

python

import sqlite3
from contextlib import closing

with closing(sqlite3.connect("catalog-lab.db")) as connection:
    connection.execute("PRAGMA foreign_keys = ON")
    rows = connection.execute(
        "SELECT item_id, name FROM catalog_items WHERE name = ?",
        ("Sensor Kit",),
    ).fetchall()

print(rows)

Placeholders can only bind values and cannot safely substitute table names, column names, or SQL keywords. Dynamic identifiers require an allowlist and explicit mapping.

Constraint Failure Is a Valuable Test

Each statement below should fail. Run them separately in the interactive CLI. With .read or batch input and .bail on, the first failure stops execution, so separate runs are needed to test both constraints.

sql

-- negative money violates CHECK
INSERT INTO catalog_items (
    item_id, item_type_id, name, value_cents, discovered_on
) VALUES (6, 1, 'Error sample', -1, '2024-01-01');

-- missing parent violates FOREIGN KEY when enforcement is enabled
INSERT INTO catalog_items (
    item_id, item_type_id, name, value_cents, discovered_on
) VALUES (7, 999, 'Orphan samples', 100, '2024-01-01');

Expect CHECK constraint failed and FOREIGN KEY constraint failed, respectively; neither sample should remain in the table. Then replace -1 with nonnumeric text to verify that STRICT rejects the type mismatch. Test both accepted and rejected states.

Lesson Summary

A relational schema is an executable data contract. Safe writes depend on explicit column names, constraints, transaction boundaries, and parameter binding, not on developers "remembering every time." In the next lesson, we'll explore SELECT, JOIN, aggregation, subqueries, and execution plans on this set of tables.

Chapter 1: Propositional Logic: From Requirement Sentences to Decidable Formulas

Chapter 2: Sets and Operations: Describing Object Scope via Membership

Chapter 3: Direct Proof, Contrapositive, and Case Analysis: Building Verifiable Reasoning Chains

Chapter 4: Recursive Definitions and Mathematical Induction: Proving Along the Construction Process

Chapter 5: Binary Relations: Properties, Composition, and Closure

Chapter 6: Counting Principles: Addition, Multiplication, Inclusion-Exclusion, and the Pigeonhole Principle

Chapter 7: Graph Models and Traversal: First Define Edges, Then Choose Representation

Chapter 8: Probability Space and Conditional Probability: First Build the Model, Then Update Your Judgment

Chapter 9: Divisibility, Congruence, and Euclid's Algorithm: The Fundamental Structure of Integers

Chapter 10: Vectors and Inner Products: Turning Objects into Comparable Coordinates

Chapter 11: Limits, Derivatives, and the Chain Rule: Describing Local Change

Chapter 12: Self-Information, Entropy, and Coding: How Uncertainty Becomes Bits