SQL DROP TABLE

Summary: in this tutorial, you will learn how to use SQL DROP TABLE statement to remove tables in a database.

SQL DROP TABLE statement is used to remove a table in a database. When you use the SQL DROP TABLE statement to remove a table, the database engine deletes all objects associated with that table including data, table structure, indexes, constraints, triggers, and maybe privileges.

The syntax of SQL DROP TABLE is as follows:

DROP TABLE table_nameCode language: SQL (Structured Query Language) (sql)

You need to specify the table name that you want to remove after the DROP TABLE keywords. To remove a table, you must have drop table privilege. If the query runs successfully, the table will be removed from the database permanently.

If you want to remove a table in a different database, you need to use the following syntax:

DROP TABLE database_name.table_nameCode language: SQL (Structured Query Language) (sql)

In the above syntax, you explicitly specify the table that you want to remove in a specific database.

Let’s practice the SQL DROP TABLE statement with some examples.

SQL DROP TABLE examples

Here is an example of removing table projects in the database:

DROP TABLE projectsCode language: SQL (Structured Query Language) (sql)

To remove the projects table in the mydb database, you use the following query:

DROP TABLE tmp.projectsCode language: SQL (Structured Query Language) (sql)

SQL DROP TABLE – important notes

• You should take extra caution when using the SQL DROP TABLE statement. You have to check whether the table that is being removed is still useful for references in the future. If this is the case, you can rename it or make a backup before removing it.
• You cannot use the SQL DROP TABLE to remove a table that is referenced by a FOREIGN KEY constraint. To drop the table, you must drop the referencing table first.
• It is safer to specify the database name when you use the SQL DROP TABLE statement. Because in the database landscape, you may have different database systems for different purposes such as development, QA, and production. Sometimes you have to go to those systems at the same time to do the work. You may confuse the production, QA, and development systems. As a result you may accidentally drop the table in the production system with the thought that it is a development system.

In this tutorial, you have learned how to use SQL DROP TABLE statement to remove tables from a database.