MySQL DROP TABLE Statement
Last Updated :
24 Jun, 2024
MySQL is a free and open-source relational database management system written in C and C++ that is extremely popular among developers. Like other relational database management systems, MySQL provides various rich features to create databases and tables, insert data in them, and further manipulate them as the system evolves. In this article, we are going to have a look at one such functionality, the DROP TABLE statement.
DROP TABLE in MySQL
The DROP TABLE statement drops one or more existing tables from the database. It removes the table definition and all the data inside the table, so be careful while using this statement.
Syntax:
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
Parameters:
- IF EXISTS: This optional clause ensures that no error is thrown if the table does not exist.
- table_name: The name of the table you want to delete. Multiple tables can be specified, separated by commas.
- RESTRICT | CASCADE: These optional keywords are used to determine the behavior if there are dependencies. In MySQL, these keywords are recognized but not enforced. The default behavior is to drop the table even if there are dependencies.
Use the DROP Statement to Remove a Table
Let's start by creating some tables and inserting records in it. We will later use these tables in the subsequent examples to understand the DROP TABLE statement better.
The first table we are going to create is the EMPLOYEE table. The following query creates the table and then later inserts records in it.
-- create employee table
CREATE TABLE EMPLOYEE (
empId INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept TEXT NOT NULL
);
-- insert into employee table
INSERT INTO EMPLOYEE VALUES (1, 'Clark', 'Sales');
INSERT INTO EMPLOYEE VALUES (2, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE VALUES (3, 'Ava', 'Sales');
We will run the following query to fetch the initial data in the table:
SELECT * FROM EMPLOYEE;
The following is the initial data in the table:
Initial DataThe second table, we are going to create is the MANAGER table. The following query creates the table and then later inserts records in it:
-- create manager table
CREATE TABLE MANAGER (
managerId INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
-- insert into manager table
INSERT INTO MANAGER VALUES (99, 'Jake');
INSERT INTO MANAGER VALUES (98, 'Smith');
INSERT INTO MANAGER VALUES (97, 'Lucy');
We will run the following query to fetch the initial data in the table:
SELECT * FROM MANAGER;
The following is the initial data in the table
Initial DataThe tables that we have created are all permanent tables, i.e. they will be persisted even after the session is closed. Now let's create a temporary table. The temporary tables do not persist and are only present for the duration of the current session. We will create a temporary table ORGANISATION using the records we already have in the EMPLOYEE and MANAGER tables. The following query creates the table and populates it with records:
-- create temporary table organisation
CREATE TEMPORARY TABLE ORGANISATION
AS
SELECT empId, name FROM EMPLOYEE
UNION ALL
SELECT managerId, name FROM MANAGER;
We will run the following query to fetch the initial data in the table:
SELECT * FROM ORGANISATION;
The following is the initial data in the table
Initial DataDropping a Temporary Table
Example: Dropping Temporary Table and Handling Non-Existent Table Error
In this example, we will see how we can drop a temporary table. The following query drops the table ORGANISATION that we created above.
DROP TEMPORARY TABLE ORGANISATION;
Note that we used the TEMPORARY keyword to specify to the engine that we are deleting a temporary table.
Now if we run the following query:
SELECT * FROM ORGANISATION;
We will get the following error:
ERROR 1146 (42S02) at line line_num: Table 'db_name.organisation' doesn't exist
Explanation: Dropping the temporary table ORGANISATION using DROP TEMPORARY TABLE removes it from the session. Attempting to query it afterward results in an "ERROR 1146" as the table no longer exists. This underscores the ephemeral nature of temporary tables and the need for careful session-specific management.
Dropping Multiple Tables
Example: Dropping Multiple Tables and Handling Subsequent Query Errors
In this example let's try to delete multiple tables. The following query drops the tables EMPLOYEE and MANAGER tables that we created.
DROP TABLE EMPLOYEE, MANAGER;
Now if we try to use the above tables in any subsequent query we will get an error message.
Explanation: Executing the query DROP TABLE EMPLOYEE, MANAGER; removes both the EMPLOYEE and MANAGER tables. Subsequent attempts to use these tables in queries result in error messages, specifically indicating that the tables no longer exist.
Handling Non-Existent Table with DROP TABLE IF EXISTS
Example: Handling Non-Existent Table with DROP TABLE IF EXISTS
In this example, we will try to drop a table that doesn't exist. The following query tries to drop table STUDENT which does not exist.
DROP TABLE STUDENT;
Once you run this command, you will get the following query.
ERROR 1146 (42S02) at line line_num: Table 'db_name.student' doesn't exist
We can avoid the error message by running the following query:
DROP TABLE IF EXISTS STUDENT
Here we made use of the IF EXISTS keyword. Executing the above query doesn't throw any error.
Explanation: The initial attempt to drop the non-existent table STUDENT results in an "ERROR 1146" as it's not found. Using the DROP TABLE IF EXISTS STUDENT prevents errors by checking for existence before attempting to drop, providing a smooth execution even if the table doesn't exist.
Conclusion
In this article, we went through the DROP TABLE statement. We had a chance to look at the different use cases as well as different keywords that we can use along the statement. As you can see the DROP TABLE statement provides the developer immense power in playing with data and tables. However, one should be careful about using the DROP TABLE statement as once committed there is no way to retrieve the data back.
Similar Reads
SQL Interview Questions
Are you preparing for a SQL interview? SQL is a standard database language used for accessing and manipulating data in databases. It stands for Structured Query Language and was developed by IBM in the 1970s, SQL allows us to create, read, update, and delete data with simple yet effective commands.
15+ min read
SQL Tutorial
SQL is a Structured query language used to access and manipulate data in databases. SQL stands for Structured Query Language. We can create, update, delete, and retrieve data in databases like MySQL, Oracle, PostgreSQL, etc. Overall, SQL is a query language that communicates with databases.In this S
11 min read
Non-linear Components
In electrical circuits, Non-linear Components are electronic devices that need an external power source to operate actively. Non-Linear Components are those that are changed with respect to the voltage and current. Elements that do not follow ohm's law are called Non-linear Components. Non-linear Co
11 min read
SQL Commands | DDL, DQL, DML, DCL and TCL Commands
SQL commands are crucial for managing databases effectively. These commands are divided into categories such as Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), Data Query Language (DQL), and Transaction Control Language (TCL). In this article, we will e
7 min read
SQL Joins (Inner, Left, Right and Full Join)
SQL joins are fundamental tools for combining data from multiple tables in relational databases. Joins allow efficient data retrieval, which is essential for generating meaningful observations and solving complex business queries. Understanding SQL join types, such as INNER JOIN, LEFT JOIN, RIGHT JO
6 min read
Normal Forms in DBMS
In the world of database management, Normal Forms are important for ensuring that data is structured logically, reducing redundancy, and maintaining data integrity. When working with databases, especially relational databases, it is critical to follow normalization techniques that help to eliminate
8 min read
Spring Boot Tutorial
Spring Boot is a Java framework that makes it easier to create and run Java applications. It simplifies the configuration and setup process, allowing developers to focus more on writing code for their applications. This Spring Boot Tutorial is a comprehensive guide that covers both basic and advance
10 min read
Class Diagram | Unified Modeling Language (UML)
A UML class diagram is a visual tool that represents the structure of a system by showing its classes, attributes, methods, and the relationships between them. It helps everyone involved in a projectâlike developers and designersâunderstand how the system is organized and how its components interact
12 min read
ACID Properties in DBMS
In the world of Database Management Systems (DBMS), transactions are fundamental operations that allow us to modify and retrieve data. However, to ensure the integrity of a database, it is important that these transactions are executed in a way that maintains consistency, correctness, and reliabilit
8 min read
Steady State Response
In this article, we are going to discuss the steady-state response. We will see what is steady state response in Time domain analysis. We will then discuss some of the standard test signals used in finding the response of a response. We also discuss the first-order response for different signals. We
9 min read