Inserting into Parent and Child Tables with Immediate Foreign Key Constraints

Issue Overview: Atomic Insertion of Journal Entries and Balanced Transactions

The core challenge revolves around designing a double-entry bookkeeping system in SQLite where a parent table (journal) and a child table (trans) must be populated atomically. Each transaction in trans requires a valid journal_id referencing the newly inserted journal entry. A trigger (verify_trans) enforces data integrity by ensuring that the sum of credits (cr) across all transactions linked to a journal entry equals zero. The critical problem is that SQLite does not natively support inserting into both tables in a single statement while dynamically referencing the auto-incremented journal.id in the subsequent trans inserts.

Schema and Trigger Context
The journal table stores high-level details of financial entries, while trans records individual debit/credit transactions. The foreign key constraint (trans.journal_id REFERENCES journal.id) enforces referential integrity. The verify_trans trigger fires after an insert into journal, querying the trans table to validate that the sum of credits equals zero. This design assumes that all related trans rows are already present when the trigger executes, which is not the case if trans inserts occur after journal.

Functional Requirements

1. Atomicity: The insertion of a journal entry and its associated transactions must succeed or fail as a unit.
2. Referential Integrity: trans.journal_id must reference a valid journal.id.
3. Data Validation: The trigger must verify that transactions balance (sum to zero) immediately upon journal insertion.

Technical Constraints

• SQLite’s RETURNING clause cannot pass auto-generated IDs (e.g., journal.id) to subsequent inserts in the same statement.
• Triggers execute in the context of the statement that invoked them, creating timing issues when dependent data is not yet committed.

Possible Causes: Limitations in SQLite’s Transactional Semantics and Trigger Execution

1. Immediate Foreign Key Enforcement

SQLite enforces foreign key constraints by default unless DEFERRABLE INITIALLY DEFERRED is explicitly specified. In the original schema, trans.journal_id references journal.id without deferral, meaning the referenced journal.id must exist at the moment the trans row is inserted. This makes it impossible to insert trans rows in the same statement as their parent journal entry because the journal.id is generated during the insert and cannot be referenced until after the statement completes.

2. Trigger Timing and Isolation

The verify_trans trigger is defined as AFTER INSERT ON journal, meaning it executes immediately after a journal row is inserted. However, if the corresponding trans rows are inserted in a separate statement, the trigger will not "see" those rows unless they are part of the same transaction. This leads to a race condition: the trigger may fire before the trans rows are inserted, causing false validation failures.

3. CTE and RETURNING Clause Limitations

SQLite’s Common Table Expressions (CTEs) allow chaining multiple operations in a single statement. However, the RETURNING clause in an INSERT statement cannot propagate values to subsequent inserts in the same CTE. For example:

WITH j AS (INSERT INTO journal (narrative) VALUES ('Payment') RETURNING id)
INSERT INTO trans (journal_id, account, cr) 
VALUES ((SELECT id FROM j), 'Cash', -100), 
       ((SELECT id FROM j), 'Revenue', 100);

This fails because the RETURNING clause’s output is not accessible to the second INSERT. The id from journal remains unavailable until the entire statement completes, violating the foreign key constraint during the trans insert.

4. Auto-Increment ID Generation

SQLite’s AUTOINCREMENT keyword ensures that primary keys are monotonically increasing but does not guarantee contiguous values. The last_insert_rowid() function retrieves the most recently generated ID, but this value is scoped to the current database connection and statement. Attempting to use last_insert_rowid() mid-statement (e.g., in a trigger) may return unexpected results due to statement-level atomicity.

Troubleshooting Steps, Solutions & Fixes: Schema Adjustments, Transaction Management, and Trigger Optimization

1. Defer Foreign Key Enforcement

Modify the trans table definition to defer foreign key checks until the transaction commits:

CREATE TABLE trans (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  journal_id INTEGER NOT NULL,
  account TEXT NOT NULL,
  cr INTEGER,
  FOREIGN KEY(journal_id) REFERENCES journal(id) 
    DEFERRABLE INITIALLY DEFERRED
);

This allows inserting trans rows with a journal_id that does not yet exist in journal, provided both inserts occur within the same transaction.

Implementation Steps

1. Begin an explicit transaction.
2. Insert the journal row and retrieve last_insert_rowid().
3. Insert trans rows using the retrieved journal_id.
4. Commit the transaction.

Example

BEGIN TRANSACTION;
INSERT INTO journal (narrative) VALUES ('Payment');
INSERT INTO trans (journal_id, account, cr) 
VALUES (last_insert_rowid(), 'Cash', -100),
       (last_insert_rowid(), 'Revenue', 100);
COMMIT;

Advantages

• Ensures atomicity and referential integrity.
• Avoids trigger false positives by committing all changes simultaneously.

Drawbacks

• Requires splitting inserts into multiple statements.
• Relies on application logic to manage transactions.

2. Trigger Redesign for Delayed Validation

Modify the verify_trans trigger to execute only when a journal entry is marked as finalized, rather than immediately after insertion. This allows inserting journal and trans rows in separate statements while deferring validation until all related data is present.

Schema Changes
Add a finalized column to journal:

ALTER TABLE journal ADD COLUMN finalized BOOLEAN NOT NULL DEFAULT 0;

Update the trigger to check only finalized entries:

CREATE TRIGGER verify_trans AFTER UPDATE OF finalized ON journal
WHEN NEW.finalized = 1
BEGIN
  SELECT CASE WHEN SUM(cr) <> 0 THEN 
    RAISE(ROLLBACK, 'Transactions don't balance') 
  END
  FROM trans 
  WHERE trans.journal_id = NEW.id;
END;

Workflow

1. Insert the journal row with finalized = 0.
2. Insert trans rows referencing the new journal.id.
3. Update the journal row to set finalized = 1, invoking the trigger.

Example

INSERT INTO journal (narrative, finalized) VALUES ('Payment', 0);
INSERT INTO trans (journal_id, account, cr) 
VALUES (last_insert_rowid(), 'Cash', -100),
       (last_insert_rowid(), 'Revenue', 100);
UPDATE journal SET finalized = 1 WHERE id = last_insert_rowid();

Advantages

• Decouples insertion from validation, allowing flexible data entry.
• Reduces trigger overhead by executing only on finalization.

Drawbacks

• Adds complexity with an additional column.
• Requires careful handling of unfinalized entries in application logic.

3. Temporary Journal ID Assignment

Use a placeholder value for journal.id during insertion and update it to a valid auto-incremented ID after both tables are populated. This leverages SQLite’s support for ON UPDATE CASCADE to propagate changes to child rows.

Schema Changes
Modify the journal and trans tables to enable cascading updates:

CREATE TABLE journal (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  narrative TEXT NOT NULL DEFAULT ""
);

CREATE TABLE trans (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  journal_id INTEGER NOT NULL,
  account TEXT NOT NULL,
  cr INTEGER,
  FOREIGN KEY(journal_id) REFERENCES journal(id) 
    ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
);

Workflow

1. Begin a transaction.
2. Insert trans rows with a placeholder journal_id (e.g., -1).
3. Insert a journal row with id = -1.
4. Update the journal row’s id to a valid value, which cascades to trans.journal_id.
5. Commit the transaction.

Example

BEGIN TRANSACTION;
INSERT INTO trans (journal_id, account, cr) 
VALUES (-1, 'Cash', -100),
       (-1, 'Revenue', 100);
INSERT INTO journal (id, narrative) VALUES (-1, 'Payment');
UPDATE journal SET id = (SELECT MAX(id) + 1 FROM journal) WHERE id = -1;
COMMIT;

Advantages

• Allows inserting both tables in any order.
• Automatically propagates ID changes to child rows.

Drawbacks

• Risks primary key conflicts if placeholders are not managed carefully.
• Requires disabling AUTOINCREMENT or handling edge cases in ID generation.

4. Single-Table Schema Redesign

Flatten the schema by merging journal and trans into a single table, eliminating the foreign key dependency. This approach simplifies inserts but may reduce normalization.

Schema Example

CREATE TABLE journal_entry (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  narrative TEXT NOT NULL DEFAULT "",
  account TEXT NOT NULL,
  cr INTEGER
);

CREATE TRIGGER verify_journal_entry AFTER INSERT ON journal_entry
BEGIN
  SELECT CASE WHEN SUM(cr) <> 0 THEN 
    RAISE(ROLLBACK, 'Transactions don't balance') 
  END
  FROM journal_entry 
  WHERE id = NEW.id;
END;

Insertion Example

INSERT INTO journal_entry (narrative, account, cr) 
VALUES ('Payment', 'Cash', -100),
       ('Payment', 'Revenue', 100);

Advantages

• Eliminates foreign key complexity.
• Ensures atomic inserts.

Drawbacks

• Duplicates narrative and other journal-level data across rows.
• Increases storage overhead.

5. Application-Level Transaction Management

Handle the insertion logic entirely within the application by:

1. Starting a transaction.
2. Inserting the journal row.
3. Retrieving last_insert_rowid().
4. Inserting trans rows with the retrieved ID.
5. Committing the transaction.

Example in Pseudocode

def create_journal_entry(narrative, transactions):
    with db.connection() as conn:
        cursor = conn.cursor()
        cursor.execute("BEGIN TRANSACTION")
        cursor.execute(
            "INSERT INTO journal (narrative) VALUES (?)", 
            (narrative,)
        )
        journal_id = cursor.lastrowid
        for txn in transactions:
            cursor.execute(
                "INSERT INTO trans (journal_id, account, cr) VALUES (?, ?, ?)",
                (journal_id, txn['account'], txn['cr'])
            )
        cursor.execute("COMMIT")

Advantages

• Full control over insertion order and error handling.
• No schema modifications required.

Drawbacks

• Relies on application logic to maintain integrity.
• Vulnerable to programming errors if not rigorously tested.

6. Trigger-Controlled ID Propagation

Use a BEFORE INSERT trigger on trans to dynamically set journal_id based on the latest journal entry. This approach is risky and not recommended due to concurrency issues.

Trigger Example

CREATE TRIGGER set_journal_id BEFORE INSERT ON trans
WHEN NEW.journal_id IS NULL
BEGIN
  SELECT CASE 
    WHEN (SELECT COUNT(*) FROM journal) = 0 THEN 
      RAISE(ROLLBACK, 'No journal entry exists')
    ELSE 
      NEW.journal_id := (SELECT MAX(id) FROM journal)
  END;
END;

Insertion Example

INSERT INTO journal (narrative) VALUES ('Payment');
INSERT INTO trans (account, cr) VALUES ('Cash', -100), ('Revenue', 100);

Advantages

• Simplifies insertion by inferring journal_id automatically.

Drawbacks

• Fails in concurrent environments where multiple journal inserts occur simultaneously.
• Relies on MAX(id), which may not correspond to the intended journal entry.

Conclusion

The optimal solution depends on the application’s requirements for concurrency, simplicity, and data integrity. For most use cases, deferring foreign key checks combined with explicit transaction management provides a robust balance between atomicity and performance. For systems requiring strict immediate validation, delayed trigger execution via a finalized flag offers greater control. Developers should avoid workarounds that compromise SQLite’s transactional guarantees or introduce concurrency risks.