Hacktoberfest 2026 : les issues que les mainteneurs ont marquées pour octobre, ouvertes et accessibles aux débutants. Parcourir les issues Hacktoberfest

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

Ouverte
  • Quelque chose ne va pas ?
#528 1 commentaire 0 réactions 0 personnes assignées Voir sur GitHub

Les mainteneurs répondent en général sous 1 jour

Personne n'a encore pris cette issue.

Évaluation

Difficulté
5/5
Temps estimé
Plus d'une semaine
Accessibilité débutants
35/100
Type d'issue
Bug
Clarté
Plutôt claire
Activité
Active
Stack technique
php, sqlite
Domaine
backend, databases

Piste de recherche

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.

Rédigé par le modèle d'indexation à partir du texte de l'issue.

Description

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.

Langage dominant
PHP
Étoiles
362
Forks
69
Merge moyen
3 j 11 h
PR mergées (30 j)
12

Préparer son environnement

Ouvrir dans Codespaces

Lance le conteneur de développement du projet dans votre navigateur, avec votre propre compte GitHub.

  • Aucun Dockerfile ni fichier Docker Compose
  • Aucun modèle de pull request
  • Aucun guide de contribution

Par où commencer

  1. Lisez l'issue en entier, puis le guide de contribution du projet.
  2. Signalez en commentaire que vous la prenez — cela évite que deux personnes fassent le même travail.
  3. Forkez le dépôt et travaillez sur une branche.
  4. Ouvrez une pull request qui référence le numéro de l'issue.

Autres issues de WordPress/sqlite-database-integration

Toutes les issues de WordPress/sqlite-database-integration

Issues similaires

Plus d'issues PHP

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.