MySQL Drop Table

We can drop a table in MySQL using the DROP table statement. 

MySQL Drop Table syntax

The basic syntax of the drop table is,

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]

Users need to have the DROP privilege to be able to drop a table.

Pre-requisites

Command-line

• Log in to the command-line client with user credentials who has the DROP privilege.
• Select the database from which we have to drop the tables.

SQL Workbench

• Open a new connection using user credentials who has the DROP privilege.
• Open a new SQL tab.
• Select a database with the USE statement.

Drop a table in MySQL using the command line

• View all tables using the command:

show tables;

• To drop a table, the syntax is:

drop table <table-name> or <list of comma-separated table-names>

Example:

DROP table performance;

Drop a table in MySQL referenced by other tables

• If we want to drop a table whose columns are foreign keys for other tables, we first need to drop the child table.
• If we attempt to drop such a table, MySQL triggers an error.

For example, the emp_id column of the "employees" table is a foreign key in the "employee_dept" table. If we try to drop the employees directly, we get the error shown in the screenshot below.

drop table employees;

• To correct this error, we would first have to drop the "employee_dept" table and then drop the "employees" table.

Drop a temporary table in MySQL using MySQL workbench

• Consider that we have a temporary table called "MAX_SALARY".
• Max_salary table creation query is:

Create TEMPORARY TABLE MAX_SALARY

 SELECT E.FIRST_NAME,E.LAST_NAME,TEMP.SALARY,ED.DEPT_ID,D.DEPT_NAME FROM 

      (SELECT MAX(SALARY) AS 'SALARY', DEPT_ID FROM EMPLOYEES E, EMPLOYEE_DEPT ED WHERE E.EMP_ID = ED.EMP_ID GROUP BY ED.DEPT_ID) AS TEMP 

      INNER JOIN EMPLOYEE_DEPT ED ON TEMP.DEPT_ID = ED.DEPT_ID

      INNER JOIN EMPLOYEES E ON E.EMP_ID = ED.EMP_ID AND TEMP.SALARY = E.SALARY

      INNER JOIN DEPARTMENTS D ON D.DEPT_ID = ED.DEPT_ID;

• If we want to drop the above table, we use the syntax:

DROP TEMPORARY TABLE <table_name>;

Example:

DROP TEMPORARY TABLE MAX_SALARY;