The DROP Statement in SQL – Tutorial

The DROP Statement in SQL: Overview

            You use the DROP statement in SQL to delete created database objects. You can use the DROP statement in SQL to delete databases, tables and indexes. You can also use the DROP statement in SQL in conjunction with the ALTER TABLE statement to delete fields from tables. Note that this is NOT the statement used to delete specified data from tables. You can delete specified data from tables by using the SQL DELETE statement, instead. The following statement shows the core SQL used to delete a database. Note that the parameter “database_name” is the name of the database to delete.

DROP DATABASE database_name

            The next statement is the core SQL statement you use to delete a table within a database. Note that the “table_name” parameter is the name of the table within the database to delete.

DROP TABLE table_name

            Note that while you cannot select records to delete with the DROP statement in SQL, there is a statement that is considered part of the data definition language in SQL that will delete a table and then re-create the table, thereby effectively deleting all records from the table. This statement is the TRUNCATE TABLE statement. Because it deletes and then re-creates the table, versus selecting individual records to remove from a table, it is considered part of the data definition language versus the data manipulation language. The following statement shows the core SQL used within the TRUNCATE TABLE statement. Note that the “table_name” parameter is the name of the table to delete and then re-create.

TRUNCATE TABLE table_name

The DROP Statement in SQL – Tutorial: A picture of the general syntax of the DROP DATABASE, DROP TABLE, and TRUNCATE TABLE commands in SQL.

            You can also use the DROP statement in SQL to delete table indexes. The core SQL of this statement is essentially DROP INDEX, however, it is implemented in slightly different ways within various relational database management systems. The following statement shows the core SQL used within the DROP INDEX statement. Note that the “index_name” parameter is the name of the index to delete.

DROP INDEX index_name

            The implementation of this statement within Microsoft SQL Server is slightly different from the core SQL, in that the parameter needs to know the name of the table associated with the index as well as the index name. The following example shows the DROP INDEX statement in SQL Server. Note that the “table_name” parameter is the name of the table associated with the index to delete and the “index_name” parameter is the name of the index to delete.

DROP INDEX table_name.index_name;

            In MySQL, you only use the DROP INDEX statement within the ALTER TABLE statement. So, if using MySQL, the statement would be entered as shown below. Note that the “table_name” parameter is the name of the table associated with the index to delete and the “index_name” parameter is the name of the index to delete.

ALTER TABLE table_name DROP INDEX index_name

            Below is a listing of hyperlinks that demonstrate the various implementations of the DROP statement in SQL within MySQL 5.7, SQL Server 2012, and Access 2013.

MySQL 5.7 (DROP DATABASE):

http://dev.mysql.com/doc/refman/5.7/en/drop-database.html

SQL Server 2012 (DROP DATABASE):

http://technet.microsoft.com/en-us/library/ms178613.aspx

MySQL 5.7 (DROP TABLE):

http://dev.mysql.com/doc/refman/5.7/en/drop-table.html

SQL Server 2012 (DROP TABLE):

http://technet.microsoft.com/en-us/library/ms173790.aspx

MySQL 5.7 (TRUNCATE TABLE):

http://dev.mysql.com/doc/refman/5.7/en/truncate-table.html

SQL Server 2012 (TRUNCATE TABLE):

http://msdn.microsoft.com/en-us/library/ms177570.aspx

MySQL 5.7 (DROP INDEX):

http://dev.mysql.com/doc/refman/5.7/en/drop-index.html

SQL Server (DROP INDEX):

http://technet.microsoft.com/en-us/library/ms176118.aspx

Access 2013 (DROP Statement)

http://msdn.microsoft.com/en-us/library/office/ff821409.aspx

The DROP Statement in SQL: Video Lesson

            The following video lesson, titled “The DROP Statement,” shows the general syntax of using the DROP statement in SQL within different SQL implementations. This video lesson is from our introductory SQL course, titled “Mastering Introductory SQL Made Easy v.1.0.”