Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
You can specify ON DELETE CASCADE for foreign keys constraint(by default it's NO ACTION ). Beware cascade operations potentially may cause deadlocks though... –  a1ex07 May 25 '13 at 17:28
    
@All Do you want to DELETE rows FROM test or to DROP TABLE test? –  ypercube May 25 '13 at 19:24
    
@ypercube drop the entire table. –  All May 26 '13 at 18:33
    
Then edit your question, using DROP in place of DELETE. And (I guess) that if there FKs that reference the tables (that reference the one), you want those (grandchildren and grand...grandchildren tables) dropped, too. –  ypercube May 26 '13 at 18:38
    
@ypercube good suggestion of terminology. yes, I want to drop all children, grandchildren; what I need to do manually. –  All May 26 '13 at 19:12
add comment

1 Answer

up vote 2 down vote accepted

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;

Give it a Try !!!

share|improve this answer
1  
the OP has clarified that he wants to DROP the tables, not DELETE rows from them. –  ypercube May 26 '13 at 18:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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