SQL Drop Table

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

Copy
DROP TABLE table_name;

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.

Copy
CREATE TABLE `test`.`customer` 
( `CustomrId` INT NOT NULL, `CustomerName` VARCHAR(45) NULL,
  `ProductId` VARCHAR(45) NOT NULL, `State` VARCHAR(45) NULL, 
  PRIMARY KEY (`CustomrId`, `ProductId`), 
  UNIQUE INDEX `CustomrId_UNIQUE` (`CustomrId` ASC) VISIBLE);

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

Check If Table Exists Using SQL SELECT

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

Query

Copy
DROP TABLE customer;

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.

Copy
CREATE TABLE "test.customer"
 ( "CustomerId" integer NOT NULL,
   "CustomerName" character varying(45),
   "ProductId" character varying(45),
   "Country" character varying(45),
   PRIMARY KEY ("CustomerId","ProductId") );

SQL Create Table On PostgreSQL

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

PostgreSQL SELECT Table

Now we will drop the table using below query.

Copy
DROP table public."Customer";

PostgreSQL DROP Table

SQL Server Drop Table

Create a Customer table using below query.

Copy
CREATE TABLE customer 
( CustomrId INT NOT NULL,
  CustomerName VARCHAR(45) NULL,
  ProductId VARCHAR(45) NOT NULL,
  State VARCHAR(45) NULL,
  PRIMARY KEY (CustomrId, ProductId));

SQLServer Create Table

Use SQL select to validate if the table exists.

SQLServer SELECT Table

Now we will drop the table using below query.

Copy
DROP TABLE dbo.customer;

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.