Open In App

Drop Multiple Tables in MySQL

Last Updated : 15 Sep, 2022
Improve
Improve
Like Article
Like
Save
Share
Report

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.


Previous Article
Next Article

Similar Reads

Insert multiple values into multiple tables using a single statement in SQL Server
The T-SQL function OUTPUT, which was introduced in 2005, can be used to insert multiple values into multiple tables in a single statement. The output values of each row that was part of an INSERT, UPDATE or DELETE operation are returned by the OUTPUT clause. Syntax: INSERT INTO Table1 (Col1, Col2) OUTPUT inserted.Col1, inserted.Col2 INTO Table2 VAL
1 min read
Truncate All Tables in MySQL
TRUNCATE statement is a Data Definition Language (DDL) operation that is used to mark the extent of a table for deallocation (empty for reuse). The result of this operation quickly removes all data from a table, typically bypassing a number of integrity-enforcing mechanisms. In SQL, truncate is used to delete all data from the table but doesn't del
3 min read
MySQL | DROP USER
The DROP USER statement in MySQL can be used to remove a user account along with its privileges from the MySQL completely.But before using the drop user statement, the privileges of the user should be revoked or in other words, if a user has no privileges then the drop user statement can be used to remove a user from the Mysql database server. Synt
2 min read
Create a drop-down list that options fetched from a MySQL database in PHP
In many scenarios, we may need to create a dropdown input that can display all the options consistent with the current state of the database. This form of input is used many times in real life and the following examples may help to understand the same. A set of students who have unique registration numbers.A set of branch names and their branch ids
4 min read
SELECT data from Multiple Tables in SQL
The statement is used to retrieve the fields from multiple tables and with the help of JOIN operations we easily fetch the records from multiple tables, Generally JOINS are used when there are common records between two tables. In this article, we will look into various types of JOIN that are used in SQL. SQL JoinsSQL joins combine two or more tabl
3 min read
How to Left Join Multiple Tables in SQL
Left Join is one of the Keywords used while writing queries in SQL. In SQL we normally use Join for the purpose of forming a new table by taking out common data like rows or records or tuples from both the tables which are having matching records in general. Here when it comes to Left Join in SQL it only returns all the records or tuples or rows fr
3 min read
Querying Multiple Tables in SQL
Here, we are going to see how to query multiple tables in SQL. For example, here, we will first create a database named “geeks” then we will create 2 tables “department” and "employee" in that database. After, that we will execute our query on the tables. Creating a Database : Use the below SQL statement to create a database called geeks: CREATE DA
2 min read
Join Multiple Tables Using Inner Join
To retrieve data from the single table we use SELECT and PROJECTION operations but to retrieve data from multiple tables we use JOINS in SQL. There are different types of JOINS in SQL. In this article, only operations on inner joins in MSSQL are discussed. Inner Join is the method of retrieval of data from multiple tables based on a required condit
3 min read
SQL - SELECT from Multiple Tables with MS SQL Server
In SQL we can retrieve data from multiple tables also by using SELECT with multiple tables which actually results in CROSS JOIN of all the tables. The resulting table occurring from CROSS JOIN of two contains all the row combinations of the 2nd table which is a Cartesian product of tables. If we consider table1 contains m rows and table2 contains n
3 min read
How to Retrieve Data from Multiple Tables in SQL?
In SQL, Retrieving data from multiple tables is a common requirement in database operations. In this article, we will explore multiple approaches to retrieving data from multiple tables in SQL. We will provide an introduction to the topic, explain two distinct approaches with their respective syntax, present detailed examples for each approach with
4 min read
Article Tags :