3

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?

3

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 !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.