mysql删除表并级联删除对表的所有引用 [英] mysql drop table and cascade delete to all references to the table

问题描述

我正在从旧系统开发新系统.新系统使用MySQL和java.我想从减少表的数量开始.当我删除一个表时，比如 X，我怎么能导致所有对 X 的引用也被删除，所以如果表 Y 对表 X 有一个 FK，那么在表 Y 上，FK 和 FK 中使用的列也被删除?

I'm developing a new system from an old system. The new system is using MySQL and java. I want to start with a reduced number of tables. When I delete a table lets say X, how can I cause all references to X to be deleted as well, so if table Y has an FK to table X then on table Y the FK and the column used in the FK get deleted as well?

简化示例:

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 的 KEY/INDEX 定义(如果包含)(未包含在此示例中)

• remove the CONSTRAINT USER_FK_sourceYID
• remove the column sourceYID
• remove any KEY/INDEX definitions based on sourceYID as well if included (not included in this example)

推荐答案

没有单一的命令可以做到这一点.处理此问题的最简单方法是删除约束，然后删除父表.没有约束，你可以自由地做到这一点.

There is no single command that can do this. The simplest way to handle this is to drop the constraint and then drop the parent table. Without the constraint, you can do this freely.

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

删除该列会自动将其从它所属的任何索引中删除.即使它是一个复合索引，它也会留下一个包含其余列的索引.下面是一些假设的示例索引，我们将看到删除列时会发生什么:

Dropping the column automatically removes it from any indexes it belongs to. Even if it's a compound index, it leaves an index with the remaining columns. Here are some hypothetical example indexes, and we'll see what happens when we remove the column:

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 列的单列索引:

The result is that index y1 is gone, and both y2 and y3 are reduced to single-column indexes containing just the userID column:

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 分析您的架构以应对此类情况.

Therefore these two are now identical indexes, and you should run pt-duplicate-key-checker to analyze your schema for such cases.

这篇关于mysql删除表并级联删除对表的所有引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！