title: mysql drop table and cascade delete to all references to the table
body: <p>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?</p>
  <p>simplified example:</p>
  <pre><code>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;
  </code></pre>
  <p>I would like to preferably issue one command that will</p>
  <pre><code>DROP TABLE `Y`
  </code></pre>
  <p>and on the user table</p>
  <ul>
  <li>remove the CONSTRAINT <code>USER_FK_sourceYID</code></li>
  <li>remove the column <code>sourceYID</code></li>
  <li>remove any KEY/INDEX definitions based on <code>sourceYID</code> as well if included (not included in this example)</li>
  </ul>