Drop Multiple Tables in MySQL

DROP statement in a relational database management system (RDBMS) is used to delete a database object such as a table, index, or view. It can be used to delete the complete database also. The general syntax of the DROP command is as follows:

Syntax:

DROP object object_name;

Example:

DROP DATABASE db;

In everyday life scenarios and live projects, we generally deal with multiple tables and multiple database objects. We may sometimes require to delete multiple objects together. The DROP command also provides a way to perform multiple deletions together. Let us see how to drop multiple database objects using the DROP command:

Method 1: DROP Multiple Tables Using DROP

Before we perform the operation to drop multiple tables, let us create 2 tables in our database:

Step 1: Creating the database.

Query:

CREATE DATABASE db;

Step 2: Set the current database as db.

Query:

USE DATABASE db;

Step 3: Create the tables in the database.

Query:

CREATE TABLE A(
  ID INT PRIMARY KEY,
  Name VARCHAR(20)
  );
  
  CREATE TABLE B(
  ID INT PRIMARY KEY,
  Name VARCHAR(20)
  );

Step 4: Dropping both tables using the DROP command.

Query:

DROP TABLE IF EXISTS A, B;

Output:

In the above image, it can be seen that on executing the command tables A and B are deleted from the database

Note: IF EXISTS is used to check whether the tables are present in the database or not. We can simply use the following command also to drop the tables:

DROP TABLE A, B;

But it may result in an error if the tables are not present in the database. So we make use of the IF EXISTS clause.

Method 2: Dropping Multiple Tables with a Foreign Key Constraint

We also make use of foreign keys in real-life databases where one column of a table references a column of another table. Let us create two tables with foreign key constraints and try to drop them.

Step 1: Creating tables with foreign key

Query:

CREATE TABLE A(
  ID INT PRIMARY KEY,
  Name VARCHAR(20)
  );
  
  CREATE TABLE B(
  ID INT PRIMARY KEY FOREIGN KEY REFERENCES A(ID),
  Name VARCHAR(20)
  );

Let us try to drop these tables using the same DROP command as earlier:

Query:

DROP TABLE IF EXISTS A, B;

Output:

This query results in an error as it is not possible to delete a table that is being referenced before deleting the table that is referring to the other table with a foreign key. So we need to modify the order of tables in the DROP command to B, A. Thus, we need to delete Table B first and then A. 

It is very difficult to determine the order of the tables provided to the DROP command when the number of tables increases and the number of foreign keys also increase. Thus we make use of the command along with the DROP command to delete tables without caring about the order of the tables in the command.

Query:

SET foreign_key_checks = 0;
DROP TABLE IF EXISTS A, B;
SET foreign_key_checks = 1;

The statement SET foreign_key_checks = 0; makes the checking of foreign key constraint False and there is no error when deleting the tables in any order.

Note: This statement can only be used in MySQL. Other databases may have different commands or syntax for the same.