MySQL Drop Table

Previous Page
Next Page

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;

Previous Page
Next Page