Hacktoberfest 2026：维护者为十月标记出来的 issue，仍然开放、适合新手。 浏览 Hacktoberfest issue

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

未关闭
  • 哪里不对？
#528 1 条评论 0 个 reaction 已指派 0 人 在 GitHub 查看

维护者通常 1 天内回复

还没有人认领这个 Issue。

评估

难度
5/5
预计耗时
一周以上
新手友好度
35/100
Issue 类型
缺陷
描述清晰度
基本清楚
活跃度
活跃
技术栈
php, sqlite
领域
backend, databases

调研方向

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.

由索引模型根据 Issue 内容生成。

描述

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.

主要语言
PHP
星标
362
派生
69
平均合并
4 天 7 小时
30 天内合并 PR
9

环境准备

在 Codespaces 中打开

在浏览器里用你自己的 GitHub 账号启动这个项目的开发容器。

  • 没有 Dockerfile 或 Docker Compose 文件
  • 没有 Pull Request 模板
  • 没有贡献指南

从这里开始

  1. 先读完整个 Issue，再读项目的贡献指南。
  2. 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
  3. Fork 仓库，在一个分支上完成修改。
  4. 提交 Pull Request，并在描述里引用这个 Issue 编号。

WordPress/sqlite-database-integration 的其他 Issue

查看 WordPress/sqlite-database-integration 的全部 Issue

相似的 Issue

更多 PHP Issue

把新 issue 发到你的邮箱

精选适合新手参与的 GitHub issue 摘要。