mysql drop table and cascade delete to all references to the table

Question

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?

simplified example:

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;

I would like to preferably issue one command that will

DROP TABLE `Y`

and on the user table

• 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)

Answer 1

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`;

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

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