Why to DROP TABLE?

While creating tables, by mistake we may provide some information of the table wrongly. The information may be a table name or column name or the data type of the column etc, In some cases, the table might create long back and that does not fit into the current requirement but still we need the table to name as per requirements or delete the table to free the used memory. There are some more requirements that may pull us to delete the existing table and create them again.

To delete the table from the database, DROP TABLE statement is used.

How to DROP TABLE?

DROP TABLE is used to delete the existing table in database. By using DROP TABLE statement, we can delete the data in table and whole structure of the table. It also deletes all the relationships and permissions of the table.

 DROP TABLE [IF EXISTS] [database_name.][schema_name.]table_name;

• Database_name - Specifies the database name where the table exists.
• Schema_name - Specifies the schema under which the table exists.
• table_name - Specifies the name of the table.

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

Dropping a simple table

In the below example, we are deleting employee_details table. This deletes the entire the table along with the data in it. The DROP statement for the same is -

 DROP TABLE employee_details; 

If the above query executed successfully, then the table employee_details gets deleted successfully.

The above DROP statement works when the table is stand alone and do not have any reference key columns from other tables. Otherwise, it will throw the below error -

Could not drop object 'employee_details' because it is referenced by a FOREIGN KEY constraint.

Delete referenced and referencing table

If a table contain foreign key and referenced by another table, SQL does not allow to drop referenced table. If we want to delete referenced table, first we have deleted foreign key, or the referencing table then delete the referenced table.

Consider the below table as an example to explain more about above scenario.

CREATE TABLE department_details (
    dept_id int PRIMARY KEY,
    dept_name VARCHAR(255),
    dept_location VARCHAR(255)
  ); 

CREATE TABLE employee_details (
   emp_id int,  emp_name varchar(255),
   designation varchar(255),
   manager_id varchar(255),
   manager_id varchar(255),
   date_of_hire date,
   salary decimal(7,2),
   dept_id int NOT NULL,
   FOREIGN KEY (dept_id) REFERENCES department_details (dept_id)
 ); 

department_details is the referenced table and employee_details is the referencing table.

As we discussed before, dropping the referenced table throws an error without deleting referencing table. So, we have to delete referencing table first and next delete the referenced table.

In this example, department_details will not get deleted until or unless employee_details gets deleted. So first delete employee_details table with below query -

DROP TABLE employee_details; 

So that all the references for the department_details get dropped. Now, we can delete department_details by using below query –

DROP TABLE department_details; 

DROP TABLE employee_details, department_details; 

SELECT * FROM department_details;