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

Immediate Enforcement Behavior

No, SQLite does not automatically validate existing data against a foreign key constraint when adding a new column via ALTER TABLE … ADD COLUMN. Even if PRAGMA foreign_keys = ON is active, the enforcement mechanism triggers on INSERT, UPDATE, and DELETE operations, but it does not perform a retroactive scan of existing rows during the schema modification itself.

Validation Mechanisms

Because the ALTER TABLE statement does not verify existing data, you must manually trigger a validation to ensure referential integrity. The primary mechanism for this is the foreign_key_check pragma.

• Scoped Check: Use PRAGMA foreign_key_check; to scan the entire database for violations.
• Table-Specific Check: Use PRAGMA foreign_key_check('child_table'); to limit the scan to the specific table modified.

If this pragma returns rows, those rows represent foreign key violations that were ignored during the ADD COLUMN operation.

Evaluation Order and Cascades

SQLite evaluates foreign key constraints in the order they were defined in the CREATE TABLE statement. When a transaction involves multiple constraints on the same parent row:

1. Order of Execution: Constraints are processed sequentially. If multiple foreign keys are triggered, they are handled in the order of their definition.
2. Cascade Interactions: ON DELETE CASCADE or ON UPDATE CASCADE actions are executed before the final commit of the transaction. If a cascade triggers further changes in other tables, those subsequent changes trigger their own foreign key constraints recursively.
3. Triggers: User-defined triggers are executed according to their timing (BEFORE or AFTER). However, foreign key constraints are checked after BEFORE triggers and before AFTER triggers for the operation that triggered them.

Verification Workflow

To safely add a foreign key to an existing table, follow this sequence:

-- 1. Add the column
ALTER TABLE child ADD COLUMN parent_id INTEGER REFERENCES parent(id);

-- 2. Identify violations
PRAGMA foreign_key_check('child');

-- 3. Remediate orphaned rows (Example: set to NULL or valid ID)
UPDATE child SET parent_id = [valid_id] WHERE parent_id IS NOT NULL AND ...;

Diagnostic Note: To provide a more specific recommendation on cascade ordering, please specify if you are using deferred foreign keys (DEFERRABLE INITIALLY DEFERRED), as this changes the validation timing from the statement level to the transaction commit level.