ALTER TABLE on a parent table deletes child rows through ON DELETE CASCADE #528

Activity

1. Nandan108 commented on Oct 13, 2026

   

   Nandan108

   on Oct 13, 2026

   ContributorAuthor

   More actions

   The same cause has a second effect. When a populated child table references the parent without a cascading action, the ALTER TABLE fails instead:

   CREATE TABLE parent (id INT PRIMARY KEY);
   CREATE TABLE child (id INT, pid INT, FOREIGN KEY (pid) REFERENCES parent (id));
   INSERT INTO parent VALUES (1), (2);
   INSERT INTO child VALUES (10, 1);
   
   ALTER TABLE parent ADD COLUMN name TEXT;
   -- SQLSTATE[23000]: Integrity constraint violation: 19 FOREIGN KEY constraint failed
   -- (MySQL: the column is added)

   This happens with ON DELETE RESTRICT, ON DELETE NO ACTION, and with no ON DELETE clause, so it affects every non-cascading foreign key. The implicit DELETE of DROP TABLE is rejected, the whole statement is rolled back, and no data is lost, but the ALTER TABLE can't succeed while the child table has rows. With an empty child table, it works.

   So for any parent table with referencing rows, ALTER TABLE currently either deletes or nulls the child rows (CASCADE, SET NULL) or fails (all other actions). Disabling foreign keys outside the transaction, as proposed above, fixes both.

   Reproduced on trunk (3.1.0) with PHP 8.3 and SQLite 3.45.1, and also seen on 3.0.2 in a real plugin upgrade.
2. JanJakes commented on Oct 20, 2026

   

   JanJakes

   on Oct 20, 2026

   Last edited by JanJakes

   Member

   More actions

   Thanks for the detailed report. I agree with the direction. MySQL doesn't support transactional DDL, so the driver can do the same:

   1. Implicitly commit any open transaction before DDL, following MySQL's list. CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE are the exceptions.
   2. For statements that recreate a table (ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE), disable foreign keys while no transaction is open.
   3. Still run each DDL statement in its own transaction. Recreating a table takes several steps (information schema, new table, copy, drop, rename, indexes), and they must stay atomic, like DDL in MySQL 8.
   4. Before committing, check PRAGMA foreign_key_check and fail on violations. Today its result is ignored.
   5. Re-enable foreign keys.

   For DROP TABLE and TRUNCATE TABLE of a referenced parent, rejecting them like MySQL sounds right, rather than disabling foreign keys.

   This also relates to #542, which needs to recreate existing tables when the driver is upgraded.
3. JanJakes

   added 4 commits that reference this issue on Oct 21, 2026

   Recreate tables when repairing temporal zero defaults

   ...

   fe98e62

   Recreate tables when repairing temporal zero defaults

   ...

   4d5d83e

   Recreate tables when repairing temporal zero defaults

   ...

   a2848be

   Recreate tables when repairing temporal zero defaults

   ...

   fe49d9a
4. Nandan108 commented on Oct 23, 2026

   

   Nandan108

   on Oct 23, 2026

   ContributorAuthor

   More actions

   Thanks, the plan sounds good to me. One point on step 4, checking PRAGMA foreign_key_check before committing:

   I'd fail only on violations that the ALTER TABLE introduced, not on ones that were already there. PRAGMA foreign_key_check lists every violating row in the database, so a single orphan row that predates the statement would make every later ALTER TABLE on that table fail, with no way forward except repairing the data by hand:

   PRAGMA foreign_keys = OFF;
   CREATE TABLE parent (id INTEGER PRIMARY KEY);
   CREATE TABLE child (id INTEGER, pid INTEGER REFERENCES parent (id));
   INSERT INTO parent VALUES (1);
   INSERT INTO child VALUES (10, 1), (20, 99);   -- 99 is an orphan
   PRAGMA foreign_key_check;                     -- reports the orphan before any ALTER
   
   -- Rebuild `child` for an unrelated change, as the SQLite procedure describes:
   -- CREATE new table, copy rows, DROP old table, RENAME.
   PRAGMA foreign_key_check;                     -- reports the same orphan again

   Orphans like this are easy to end up with, for example from rows written while foreign_keys was off, or by a driver version that didn't enforce foreign keys. MySQL doesn't re-validate existing rows on an unrelated ALTER TABLE either. I tested this on MySQL 8.0.46 and MariaDB 11.8.6, with the same results: a child table with an orphan row (inserted with foreign_key_checks=0) and one ON DELETE RESTRICT and one ON DELETE CASCADE table referencing a parent. With foreign_key_checks=1 and the orphan present, these all succeed:

   • ALTER TABLE parent ADD COLUMN ...
   • ALTER TABLE parent MODIFY ..., ALGORITHM=COPY (the parent is fully rebuilt)
   • ALTER TABLE child ADD COLUMN ...
   • ALTER TABLE child MODIFY ..., ALGORITHM=COPY (the child is fully rebuilt)

   Afterwards, the orphan is still there, and the rows of the cascading table are intact, so rebuilding the parent fired no ON DELETE action. Only ALTER TABLE child ADD CONSTRAINT ... FOREIGN KEY over the orphan fails, with error 1452.

   So failing on pre-existing violations would be stricter than MySQL: a plugin update that alters one of its tables would start failing on sites where it didn't fail before.

   A simple way is to run PRAGMA foreign_key_check before and after the rebuild and fail only when the second result has rows that the first didn't. The SQLite documentation's intent for this step is the same: to "verify that the schema change did not break any foreign key constraints".
5. JanJakes

   added 2 commits that reference this issue on Oct 23, 2026

   Repair temporal zero defaults with ALTER TABLE

   ...

   ee8a3e0

   Repair temporal zero defaults with ALTER TABLE

   ...

   0116e18