Entity Integrity, Domain Integrity, and Business Rules

Learning outcomes

A schema is trustworthy only when invalid states are difficult or impossible to store. Integrity rules protect identity, value domains, relationships, and business meaning regardless of which application, script, import job, or administrator writes the data.

Four integrity layers

These categories overlap in implementation. A PRIMARY KEY enforces entity integrity, a CHECK often enforces domain or business integrity, and a FOREIGN KEY enforces referential integrity.

Entity integrity

Entity integrity requires a key for each table occurrence. In SQL, a primary key combines uniqueness and non-nullability. Alternate candidate keys should also be protected.

CREATE TABLE warehouse (    warehouse_id INTEGER PRIMARY KEY,    warehouse_code TEXT NOT NULL UNIQUE,    display_name TEXT NOT NULL) STRICT;

warehouse_id provides the principal identity. warehouse_code is an alternate business key. Both rules matter: the generated ID supports compact references, while the code constraint prevents duplicate domain occurrences.

Domain integrity

A domain describes permitted values and operations. SQL types provide the broad domain; nullability and checks refine it.

CREATE TABLE inventory_item (    item_id INTEGER PRIMARY KEY,    sku TEXT NOT NULL UNIQUE        CHECK (length(trim(sku)) BETWEEN 3 AND 40),    item_name TEXT NOT NULL        CHECK (length(trim(item_name)) > 0),    unit_price_cents INTEGER NOT NULL        CHECK (unit_price_cents >= 0),    quantity_on_hand INTEGER NOT NULL DEFAULT 0        CHECK (quantity_on_hand >= 0),    status TEXT NOT NULL DEFAULT 'active'        CHECK (status IN ('active', 'discontinued'))) STRICT;

A default supplies a value when an insert omits the column; it does not validate an explicitly supplied value. The check constraints still define acceptable states.

Referential integrity

Foreign keys enforce valid references and lifecycle actions. They protect writes from every client that uses the database connection with enforcement enabled.

CREATE TABLE stock_movement (    movement_id INTEGER PRIMARY KEY,    warehouse_id INTEGER NOT NULL,    item_id INTEGER NOT NULL,    quantity_delta INTEGER NOT NULL        CHECK (quantity_delta <> 0),    occurred_at TEXT NOT NULL,    FOREIGN KEY (warehouse_id)        REFERENCES warehouse (warehouse_id),    FOREIGN KEY (item_id)        REFERENCES inventory_item (item_id)) STRICT;

This guarantees that every movement references a valid warehouse and item. It does not by itself guarantee that cumulative stock never becomes negative; that is a cross-row business rule.

Classify business rules by scope

Choose the simplest declarative constraint that fully expresses the rule. Declarative constraints are visible to tools, checked consistently, and often optimized by the DBMS.

Named constraints and clear failures

Server databases commonly support explicit constraint names, improving migration scripts and error interpretation. SQLite accepts named constraints in table definitions even though application error reporting is less detailed than some server systems.

CREATE TABLE reservation (    reservation_id INTEGER PRIMARY KEY,    starts_at TIMESTAMP NOT NULL,    ends_at TIMESTAMP NOT NULL,    party_size INTEGER NOT NULL,    CONSTRAINT ck_reservation_time        CHECK (ends_at > starts_at),    CONSTRAINT ck_reservation_party_size        CHECK (party_size BETWEEN 1 AND 20));

Names should describe the protected rule rather than repeat syntax, for example ck_reservation_time rather than check_1.

Constraints versus application validation

Use both where appropriate. Application validation improves experience; database constraints remain the final integrity boundary. Never rely on a UI rule as the only protection for shared data.

When triggers are justified

Triggers can enforce rules that declarative constraints cannot express, but they introduce hidden execution, ordering questions, recursion risk, and migration complexity. Prefer them when the rule must be enforced inside the database for every writer and no simpler constraint is sufficient.

CREATE TABLE order_status_audit (    audit_id INTEGER PRIMARY KEY,    order_id INTEGER NOT NULL,    old_status TEXT NOT NULL,    new_status TEXT NOT NULL,    changed_at TEXT NOT NULL) STRICT;CREATE TRIGGER trg_order_status_auditAFTER UPDATE OF status ON purchase_orderWHEN OLD.status <> NEW.statusBEGIN    INSERT INTO order_status_audit        (order_id, old_status, new_status, changed_at)    VALUES        (NEW.order_id, OLD.status, NEW.status, CURRENT_TIMESTAMP);END;

This trigger records a change; it does not define which transitions are permitted. Keep auditing and validation responsibilities explicit.

Lab: build and test an integrity boundary

PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS order_line;DROP TABLE IF EXISTS purchase_order;DROP TABLE IF EXISTS product;CREATE TABLE product (    product_id INTEGER PRIMARY KEY,    sku TEXT NOT NULL UNIQUE,    title TEXT NOT NULL CHECK (length(trim(title)) > 0),    current_price_cents INTEGER NOT NULL        CHECK (current_price_cents >= 0)) STRICT;CREATE TABLE purchase_order (    order_id INTEGER PRIMARY KEY,    status TEXT NOT NULL DEFAULT 'draft'        CHECK (status IN ('draft', 'submitted', 'cancelled')),    ordered_at TEXT,    CHECK (        (status = 'draft' AND ordered_at IS NULL)        OR        (status IN ('submitted', 'cancelled') AND ordered_at IS NOT NULL)    )) STRICT;CREATE TABLE order_line (    order_id INTEGER NOT NULL,    line_no INTEGER NOT NULL CHECK (line_no > 0),    product_id INTEGER NOT NULL,    quantity INTEGER NOT NULL CHECK (quantity > 0),    unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents >= 0),    PRIMARY KEY (order_id, line_no),    FOREIGN KEY (order_id)        REFERENCES purchase_order (order_id)        ON DELETE CASCADE,    FOREIGN KEY (product_id)        REFERENCES product (product_id)) WITHOUT ROWID;INSERT INTO product    (product_id, sku, title, current_price_cents)VALUES    (1, 'SQL-WB', 'SQL Workbook', 2499);INSERT INTO purchase_order (order_id) VALUES (1001);INSERT INTO order_line    (order_id, line_no, product_id, quantity, unit_price_cents)VALUES    (1001, 1, 1, 2, 2499);UPDATE purchase_orderSET status = 'submitted',    ordered_at = '2026-08-05T11:00:00Z'WHERE order_id = 1001;PRAGMA integrity_check;PRAGMA foreign_key_check;

-- Negative quantity violates domain/business integrity.INSERT INTO order_line    (order_id, line_no, product_id, quantity, unit_price_cents)VALUES    (1001, 2, 1, -1, 2499);-- Missing product violates referential integrity.INSERT INTO order_line    (order_id, line_no, product_id, quantity, unit_price_cents)VALUES    (1001, 2, 999, 1, 2499);-- Submitted without a timestamp violates the row-state rule.INSERT INTO purchase_order (order_id, status)VALUES (1002, 'submitted');

Common mistakes

Validating only happy-path application forms

Imports, scripts, APIs, migrations, and administrative tools can bypass one interface. Protect invariants in the database.

Using broad types without refined constraints

INTEGER permits negative quantities unless a check narrows the domain.

Putting cross-row queries inside CHECK constraints

Many DBMSs forbid or cannot safely maintain such checks. Use proper uniqueness, foreign keys, transactions, triggers, or redesigned facts.

Duplicating rules inconsistently

When the database and application implement different allowed-status lists, one will drift. Define one authoritative contract and test both layers against it.

Checkpoint and practice

Summary and next lesson

Entity integrity protects identity, domain integrity protects values, referential integrity protects references, and business integrity protects domain-specific states and transitions. Declarative constraints should be the first choice; transactions, controlled services, and triggers address wider-scope rules. The final lesson applies these ideas to a complete requirements-driven design.

References

• SQLite: CREATE TABLE and constraints
• SQLite: Foreign Key Support
• PostgreSQL: Constraints