MySQL 删除表及级联删除所有与该表相关的引用

Question

我正在从旧系统开发一个新系统。新系统使用MySQL和Java。我想从少量的表开始。如果我删除一个名为X的表，如何使所有引用X的内容也被删除，因此如果表Y具有对表X的外键，则在表Y上FK和使用FK的列也会被删除？

简化的例子：

CREATE TABLE `Y` (
    `yID` int(11) NOT NULL AUTO_INCREMENT,
    `yName` varchar(50) NOT NULL,
    ...
   ) ENGINE=InnoDB;

CREATE TABLE `user` (
    `userID` int(11) NOT NULL AUTO_INCREMENT,
    `userName` varchar(50) NOT NULL,
    `givenName` varchar(50) DEFAULT NULL,
    `sourceYID` int(11) NOT NULL,
    CONSTRAINT `USER_FK_sourceYID` FOREIGN KEY (`sourceYID`) REFERENCES `Y` (`yID`)
    ) ENGINE=InnoDB;

我希望最好只发出一个命令，这个命令将

DROP TABLE `Y`

并且在用户表上

• 移除约束条件 USER_FK_sourceYID
• 移除列 sourceYID
• 如果有的话，也一并移除基于 sourceYID 的任何键/索引定义（此示例中未包含）

- melutovich

---

我之前表述不够清晰，在这个阶段，我将使用一个精简的现有数据库结构，但是我已经导出了没有任何数据的 SQL 文件转储。我希望能够自动删除引用表中的外键和与外键相关联的列。 - melutovich

Answer 1

没有一个单一的命令可以做到这一点。最简单的处理方法是删除约束，然后再删除父表。没有约束，您就可以自由地执行此操作。

ALTER TABLE `user` DROP FOREIGN KEY `USER_FK_sourceYID`;
DROP TABLE `Y`;

删除列会自动将其从任何所属的索引中移除。即使它是复合索引，也会留下一个带有剩余列的索引。以下是一些假设的示例索引，我们将看到在删除列时会发生什么：

CREATE INDEX y1 ON `user` (sourceYID);
CREATE INDEX y2 ON `user` (userID, sourceYID);
CREATE INDEX y3 ON `user` (sourceYID, userID);

ALTER TABLE `user` DROP COLUMN `sourceYID`;

结果是索引 y1 被删除了，而 y2 和 y3 都被缩减为只包含 userID 列的单列索引：

SHOW CREATE TABLE `user`\G

CREATE TABLE `user` (
  `userID` int(11) NOT NULL AUTO_INCREMENT,
  `userName` varchar(50) NOT NULL,
  `givenName` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`userID`),
  KEY `y2` (`userID`),
  KEY `y3` (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

因此，这两个现在是相同的索引，您应该运行pt-duplicate-key-checker来分析您的架构是否存在这种情况。

- Bill Karwin

---

谢谢，这有所帮助。旧系统大约有175个表，而我对新系统的第一次尝试只涉及其中的20个表，因此我正在寻找一种自动化的方式。我已向我的新雇主发送了电子邮件，问是否使用了任何命名约定来协助处理旧系统。 - melutovich

Answer 2

SET FOREIGN_KEY_CHECKS = 0;
drop table if exists <your_1st_table>;
drop table if exists <your_2nd_table>;
SET FOREIGN_KEY_CHECKS = 1;

- samivic

---

虽然这段代码可能回答了问题，但是提供关于该代码如何以及为什么解决问题的额外背景信息会提高答案的长期价值。 - Nic3500

@samivic 抱歉你误解了问题，简单来说，我不是要删除 "your_2nd_table"，而是要删除引用 "your_1st_table" 的列。 - melutovich

好的，我认为在这种情况下，您需要禁用外键约束 SET FOREIGN_KEY_CHECKS = 0; - samivic

Answer 3

如果您有一个外键，那么您将无法删除父表，因为外键关系不允许这样做。要执行此操作，您应该执行以下步骤：

• 删除所有子记录，有两种方法：使用ON DELETE CASCADE外键选项或使用多表DELETE语句。
• 如果存在外键，则删除外键。
• 删除父表。

- Devart

---

我没有表述清楚，在这个阶段，我将使用一个减少的现有数据库结构，但是我已经导出了没有任何数据的 SQL 文件转储。我希望有一种自动化的方法来删除引用表上的外键以及与外键相关联的列（或者列）。 - melutovich

你可以添加更多信息吗？（包括你现在拥有什么和你想要什么）？ - Devart

我在问题上添加了一个示例。 - melutovich