Foreign Key Enforcement on ALTER TABLE ADD COLUMN – Current State and Unresolved Behavior

Goal

Determine how SQLite’s foreign key enforcement behaves when a new constraint is added via ALTER TABLE … ADD COLUMN on a database where PRAGMA foreign_keys = ON is active.

Constraints & Uncertainty

• SQLite 3.x – foreign key enforcement is disabled by default and must be enabled per connection.
• Existing data may already violate the new foreign key constraint.
• The SQLite documentation does not explicitly state whether DDL statements that add a foreign key trigger an immediate validation of existing rows.
• It is also unclear how SQLite orders evaluation when multiple foreign keys reference the same parent row, especially if cascade actions or user-defined triggers are involved.

Specific Questions

1. When executing ALTER TABLE child ADD COLUMN parent_id INTEGER REFERENCES parent(id);, does SQLite automatically check all existing rows in child for violations of the new foreign key?
2. If the check is not performed, what mechanisms (e.g., PRAGMA foreign_key_check) can be used to enforce the constraint after the schema change?
3. In a transaction that contains multiple foreign key constraints on the same parent row, what is the documented order of evaluation, and how does this interact with ON DELETE/UPDATE CASCADE and user-defined triggers?