In a real-time situation, there are scenarios when we would like to remove the table from the database. Let us discuss in detail about the process of removing a table from the database.

SQL DROP TABLE

The SQL DROP TABLE statement informs the database that a particular table needs to be removed. SQL DROP TABLE command removes all the data, indexes, triggers, constraints and permission specifications for that table.

We will use the following three databases for better understanding DROP Table.

  1. MySQL
  2. PostgreSQL
  3. SQL Server

MySQL Drop Table

Syntax

In the syntax above, table_name is the table that needs to be dropped.

Let us try to understand the SQL DROP TABLE using an example.

Create a table Customer using the following SQL COMMAND.

Check if the table exists using the SQL SELECT statement as shown in the below image.

Check-If-Table-Exists

Check If Table Exists Using SQL SELECT

Now we will remove the table using SQL DROP TABLE command.

Query

 

SQL-DROP-Table-on-MySQL

SQL DROP Table On MySQL

Executing the above query will remove the customer table along with all the constraints, data, index, etc.

PostgreSQL Drop Table

Create a Customer table using below query.

 

SQL Create Table On PostgreSQL

Using SQL Select check if the table exists, as shown below.

SQL-DROP-Table-on-MySQL

PostgreSQL SELECT Table

Now we will drop the table using below query.

 

SQL-DROP-Table-on-MySQL

PostgreSQL DROP Table

SQL Server Drop Table

Create a Customer table using below query.

 

SQLServer-Create-Table

SQLServer Create Table

Use SQL select to validate if the table exists.

SQLServer-SELECT-Table

SQLServer SELECT Table

Now we will drop the table using below query.

 

SQLServer-SELECT-Table

SQL Server DROP Table

Summary

SQL Drop Table command will delete the table and all the data associated with it. So it’s advisable to have a backup first before you actually drop the table.

However, if there are foreign key constraints on the table with another table then the error will be thrown. You will first have to explicitly remove the foreign-key constraints and then drop the table.

By admin

Leave a Reply

%d bloggers like this: