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 table in a database. When you use the SQL DROP TABLE statement to remove a table, the database engine deletes all objects associated to that table including data, table structure, indexes, constraints, triggers and maybe privileges.

The syntax of SQL DROP TABLE is as follows:

DROP TABLE table_name

You need to specify 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 table in a different database, you need to use the following syntax:

DROP TABLE database_name.table_nameCode language: CSS (css)

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 projects

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

DROP TABLE tmp.projectsCode language: CSS (css)

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 referencing table first.
• It is safer to specify the database name when you use 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 table in the production system with the thought that it is development system.

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