DROP TABLE statement in SQL
DROP TABLE is a SQL statement used to delete tables along with all their data, indexes, triggers, constraints and permissions. The basic statement is very simple:
DROP TABLE tableName
However, we must take into account several important considerations that we will see with examples below:
DROP TABLEadmits different arguments to make the statement more precise.
IF EXISTSonly allows the statement to be executed if the table that we want to delete actually exists on the system. We can also specify the database and/or the schema to which the table we want to delete belongs. We will use the
Orderstable that we created in the
CREATE TABLEsection as an example:
DROP TABLE IF EXISTS store.dbo.Orders;
Now if we tried to execute the statement again without including
IF EXISTS, the system would return the following error since the table doesn’t exist:
Cannot drop the table ‘store.dbo.Orders’, because it does not exist or you do not have permission.
IF EXISTS can also be used in statements like
ALTER and its use is recommended to avoid errors that can paralyze the flow of code for stored procedures, transactions, etc.
- A table can not be deleted if one of its fields refers to a Foreign Key of another table. In that case, we must remove the Foreign Key constraint from the related table or remove that table first in order to drop the first one.
For example starting again from the tables
Orders, if we wanted to delete the
Customers table with this statement, it will return the following error:
DROP TABLE IF EXISTS Customers;
Could not drop object ‘Customers’ because it is referenced by a FOREIGN KEY constraint.
This error is due to
customerId being referenced as a Foreign Key in the
Orders table. In this case to be able to drop the
Customers table we would have to options: Drop first the
Orders table or delete the Foreign Key constraint of said table using the
ALTER TABLE statement.
DROP TABLEadmits more than one table as arguments to eliminate several tables in the same statement. If there are related tables, the referenced table where the Foreign Key is defined must be written first. In case we wanted to remove the
Ordersmust go first so it doesn’t return the above error regarding the Foreign Key:
DROP TABLE IF EXISTS dbo.Orders, dbo.Customers;
DROP TABLE can also be used to delete temporary tables.