sqlite/docs/adr/ADR-003-fk-enforcement-pragma.md

# ADR-003: Foreign Key Enforcement via PRAGMA and DSN

**Status:** Accepted
**Date:** 2026-03-18

## Context

SQLite disables foreign key constraint enforcement by default for backwards compatibility.
Applications that define `REFERENCES` clauses in their schema will silently insert orphaned
rows unless they explicitly enable enforcement. This is a common source of data integrity bugs.

## Decision

Foreign key enforcement is enabled at two points:

1. **DSN parameter** — The default `Pragmas` config value includes `_fk=true`:
   ```
   ?_journal=WAL&_timeout=5000&_fk=true
   ```
   This sets `PRAGMA foreign_keys = ON` for every connection opened via the DSN.

2. **OnInit explicit PRAGMA** — After opening the database pool, `OnInit` executes an
   additional `PRAGMA foreign_keys = ON` call:
   ```go
   if _, err := db.Exec("PRAGMA foreign_keys = ON"); err != nil {
       _ = db.Close()
       return fmt.Errorf("sqlite: enable foreign keys: %w", err)
   }
   ```
   If this call fails, `OnInit` returns an error and the pool is closed, preventing startup
   with an unsafe configuration.

The redundancy is deliberate: the DSN parameter may be overridden by callers who supply a
custom `Pragmas` value, but the `OnInit` PRAGMA call always runs and fails loudly if it cannot
enforce foreign keys.

`HandleError` maps `SQLITE_CONSTRAINT_FOREIGNKEY` (error code 787) to
`xerrors.ErrInvalidInput` so that foreign key violations surface as validation errors to
callers rather than opaque internal errors.

## Consequences

**Positive:**
- Foreign key constraints are always active when using the default configuration.
- Failure to enable them is a startup error, not a silent misconfiguration.
- Violations produce a structured `xerrors.ErrInvalidInput` error.

**Negative:**
- Callers who deliberately omit `_fk=true` from a custom `Pragmas` string still get the
  enforcement applied by the `OnInit` PRAGMA. There is no opt-out without modifying the source.
- `PRAGMA foreign_keys = ON` must be set per-connection; `database/sql` connection pooling
  means this implicit approach (via DSN) can behave differently under pool pressure. The
  explicit `OnInit` PRAGMA mitigates this for the initial connection but cannot guarantee it
  for all pooled connections when `MaxOpenConns > 1`.