Is it possible to force drop of mysql tables with FK?

Question

For dropping a table in which a Foreign Key is used in other tables, it is necessary to drop all other tables having FK connection with the corresponding table.

Is there a short way to drop a table, and its all child tables (in which they have FK to the parent table)?

For example,

CREATE TABLE test
(
id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id)
) ENGINE=InnoDB

and id is a FK in 5 child tables. Is it possible to drop TABLE test, and force drop of all 5 child tables having id FK? instead of dropping child tables one by one?

Answer 1

While ON DELETE CASCADE can be attempted, it is worth noting what the output of a mysqldump looks like. At the top of every mysqldump you will see directives which disable foreign key checks and unique key checks:

-- MySQL dump 10.13  Distrib 5.5.12, for Win64 (x86)
--
-- Host: localhost    Database: sample
-- ------------------------------------------------------
-- Server version       5.5.12-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Running a reload of a mysqldump gives the DB Session the opportunity to disable those checks, load the table, then enable the checks again. In view of this, you should manually truncate all the tables in question with those two directives.

Suppose you have table test with foreign keys references in t1,t2,t3,t4,t5

Simply truncate then in order starting with child tables as follows:

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
TRUNCATE TABLE t5;
TRUNCATE TABLE t4;
TRUNCATE TABLE t3;
TRUNCATE TABLE t2;
TRUNCATE TABLE t1;
TRUNCATE TABLE test;
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

UPDATE 2018-07-05 09:33 EDT

I never circled back to this question to see the OP clarification, so let me complete this answer. What I should have posted 5 years ago was the following:

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
DROP TABLE t5;
DROP TABLE t4;
DROP TABLE t3;
DROP TABLE t2;
DROP TABLE t1;
DROP TABLE test;
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

Give it a Try !!!