Hacktoberfest 2026: los issues que los mantenedores marcaron para octubre, abiertos y aptos para principiantes. Explorar issues de Hacktoberfest

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

Abierto
  • ¿Algo no está bien?
#528 1 comentario 0 reacciones 0 asignados Ver en GitHub

Los mantenedores suelen responder en 1 día

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
5/5
Tiempo estimado
Más de una semana
Aptitud para principiantes
35/100
Tipo de issue
Error
Claridad
Bastante claro
Estado de actividad
Activo
Stack tecnológico
php, sqlite
Área
backend, databases

Línea de trabajo

Start by reading the driver's ALTER TABLE handling and transaction wrapper, then inspect how they manage SQLite foreign key settings around table recreation. The reproduction shows child rows are deleted after adding a column; verify behavior with the provided SQL, including inside a transaction. The issue asks maintainers to decide whether to emulate MySQL's implicit DDL commits, so implementation scope and expected transaction behavior need agreement first.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

Reproduction

CREATE TABLE parent (id INT PRIMARY KEY);
CREATE TABLE child (id INT, pid INT, FOREIGN KEY (pid) REFERENCES parent (id) ON DELETE CASCADE);
INSERT INTO parent VALUES (1), (2);
INSERT INTO child VALUES (10, 1), (20, 2);

ALTER TABLE parent ADD COLUMN name TEXT;

SELECT COUNT(*) FROM child;
-- 0   (MySQL: 2)

Reproduced on trunk (3.1.0) with PHP 8.3 and SQLite 3.45.1, with stringified fetches on, as in WordPress. It also happens when the ALTER TABLE runs inside START TRANSACTION ... COMMIT. With ON DELETE SET NULL, the child rows lose their references instead.

Cause

ALTER TABLE recreates the table: it creates a new table, copies the rows, drops the old table, and renames the new one. Before that, it runs PRAGMA foreign_keys = OFF, as the SQLite procedure requires. But the statement runs inside the driver's wrapper transaction, and PRAGMA foreign_keys is a no-op inside a transaction. Foreign keys stay on, so DROP TABLE performs an implicit DELETE of the old table, which fires the foreign key actions of the child tables.

Related

The same implicit DELETE affects two statements that MySQL rejects for a referenced parent table:

  • DROP TABLE parent deletes the child rows. MySQL fails with error 3730 (ER_FK_CANNOT_DROP_PARENT).
  • TRUNCATE TABLE parent deletes the child rows. MySQL fails with error 1701 (ER_TRUNCATE_ILLEGAL_FK).

A related transaction difference: MySQL implicitly commits an open transaction before and after DDL statements, but the driver runs them inside the user transaction, so a ROLLBACK undoes them together with the earlier work:

START TRANSACTION;
INSERT INTO t VALUES (1);
CREATE TABLE u (id INT);
ROLLBACK;
-- Driver: t is empty, and u doesn't exist.
-- MySQL: t has the row, and u exists.

Fix direction

Foreign keys can only be disabled outside a transaction. Emulating the implicit commit for DDL would fix both problems: with no transaction open, the driver can disable foreign keys before the wrapper transaction begins and restore them after it ends, as the SQLite procedure intends. For DROP TABLE and TRUNCATE, the driver could reject a referenced parent table as MySQL does.

This changes transaction behavior, since DDL could no longer be rolled back, so I'd like your view on the approach first. I'm happy to contribute a PR in whichever direction you prefer.

Use of AI

This report was prepared with Claude Code.

Lenguaje dominante
PHP
Estrellas
362
Forks
69
Merge medio
4 d 7 h
PR fusionados (30 d)
9

Preparar el entorno

Abrir en Codespaces

Inicia el contenedor de desarrollo del proyecto en tu navegador, con tu propia cuenta de GitHub.

  • Sin Dockerfile ni archivo de Docker Compose
  • Sin plantilla de pull request
  • Sin guía de contribución

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de WordPress/sqlite-database-integration

Todos los issues de WordPress/sqlite-database-integration

Issues similares

Más issues de PHP

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.