{"id":12143,"date":"2020-12-11T08:52:15","date_gmt":"2020-12-10T22:52:15","guid":{"rendered":"https:\/\/database.guide\/?p=12143"},"modified":"2025-11-15T11:49:26","modified_gmt":"2025-11-15T01:49:26","slug":"sql-drop-table-for-beginners","status":"publish","type":"post","link":"https:\/\/database.guide\/sql-drop-table-for-beginners\/","title":{"rendered":"SQL DROP TABLE for Beginners"},"content":{"rendered":"\n<p class=\"\">In <a href=\"https:\/\/database.guide\/what-is-sql\/\" title=\"What is SQL?\">SQL<\/a>, if you want to remove a <a href=\"https:\/\/database.guide\/what-is-a-table\/\" title=\"What is a Table?\">table<\/a> from a <a href=\"https:\/\/database.guide\/what-is-a-database\/\" title=\"What is a Database?\">database<\/a>, you need to use the <code>DROP TABLE<\/code> statement.<\/p>\n\n\n\n<p class=\"\">That destroys the table and all its data.<\/p>\n\n\n\n<!--more-->\n\n\n\n<h2 class=\"wp-block-heading\">Syntax<\/h2>\n\n\n\n<p class=\"\">The SQL standard syntax goes like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DROP TABLE &lt;table name&gt; &lt;drop behavior&gt;<\/code><\/pre>\n\n\n\n<p class=\"\">Where: <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li class=\"\"><code>&lt;table name><\/code> is the name of the table you want to drop.<\/li>\n\n\n\n<li class=\"\"><code>&lt;drop behavior><\/code> specifies any options. These can be either <code>CASCADE<\/code> or <code>RESTRICT<\/code>. <\/li>\n<\/ul>\n\n\n\n<p class=\"\">Some <a href=\"https:\/\/database.guide\/what-is-an-rdbms\/\">RDBMS<\/a>s also accept an optional <code>IF EXISTS<\/code> argument which means that it won&#8217;t return an error if the table doesn&#8217;t exist.<\/p>\n\n\n\n<p class=\"\">Some RDBMSs (such as MySQL and MariaDB) also accept an optional <code>TEMPORARY<\/code> keyword to ensure only <a href=\"https:\/\/database.guide\/what-is-a-temp-table\/\" data-type=\"post\" data-id=\"46127\">temporary tables<\/a> are dropped.<\/p>\n\n\n\n<p class=\"\">Oracle also accepts a <code>PURGE<\/code> clause, which purges it from the recycle bin. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Example<\/h2>\n\n\n\n<p class=\"\">Here&#8217;s an example to demonstrate.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DROP TABLE t1;<\/code><\/pre>\n\n\n\n<p class=\"\">Running that code drops the table called <code>t1<\/code> and all its data.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">The <code>IF EXISTS<\/code> Clause<\/h2>\n\n\n\n<p class=\"\">Here&#8217;s an example of using the <code>IF EXISTS<\/code> clause to check if the table already exists.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DROP TABLE IF EXISTS t1;<\/code><\/pre>\n\n\n\n<p class=\"\">Using <code>IF EXISTS<\/code> ensures that we don&#8217;t get an error if the table doesn&#8217;t exist.<\/p>\n\n\n\n<p class=\"\">Here&#8217;s what happens if we remove <code>IF EXISTS<\/code> from the statement:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DROP TABLE t1;<\/code><\/pre>\n\n\n\n<p class=\"\">Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Msg 3701, Level 11, State 5, Line 1\nCannot drop the table 't1', because it does not exist or you do not have permission.<\/pre>\n\n\n\n<p class=\"\">That is the message returned by SQL Server. Your message will depend on the <a href=\"https:\/\/database.guide\/what-is-a-dbms\/\" title=\"What is a DBMS?\">DBMS<\/a> that you&#8217;re using.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Dependent Foreign Keys &amp; Views<\/h2>\n\n\n\n<p class=\"\">Some RDBMSs allow an optional <code>RESTRICT<\/code> or <code>CASCADE<\/code> keyword that specifies what happens if the table has any <a href=\"https:\/\/database.guide\/what-is-a-foreign-key\/\" title=\"What is a Foreign Key?\">foreign keys<\/a> or <a href=\"https:\/\/database.guide\/what-is-a-view\/\" title=\"What is a View?\">views<\/a> that reference it.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">The <code>RESTRICT<\/code> Option<\/h3>\n\n\n\n<p class=\"\">Here&#8217;s an example of using <code>RESTRICT<\/code> when trying to drop a table that is referenced by a foreign key in another table:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DROP TABLE t1 RESTRICT;<\/code><\/pre>\n\n\n\n<p class=\"\">Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">cannot drop table t1 because other objects depend on it<\/pre>\n\n\n\n<p class=\"\">This example was done using PostgreSQL. <code>RESTRICT<\/code> is the default option, so we would have received the same result even if we hadn&#8217;t included the <code>RESTRICT<\/code> keyword.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">The <code>CASCADE<\/code> Option<\/h3>\n\n\n\n<p class=\"\">Here&#8217;s what happens if we switch over to <code>CASCADE<\/code> when trying to drop the same table (which is referenced by a foreign key in another table):<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DROP TABLE t1 CASCADE;<\/code><\/pre>\n\n\n\n<p class=\"\">Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">NOTICE:  drop cascades to constraint t2_c2_fkey on table t2\nCommands completed successfully<\/pre>\n\n\n\n<p class=\"\">This dropped the foreign key that was referencing our <code>t1<\/code> table. The foreign key was called <code>t2_c2_fkey<\/code>.<\/p>\n\n\n\n<p class=\"\">Note that it didn&#8217;t drop the table that had the foreign key. It only dropped the foreign key.<\/p>\n\n\n\n<p class=\"\">If the target table (<code>t1<\/code>) was referenced by any views, the whole view would have been dropped.<\/p>\n\n\n\n<p class=\"\">You don&#8217;t need to specify <code>CASCADE<\/code> to drop any indexes, rules, triggers, or constraints that exist for the target table. These are dropped automatically, even when using the default option (<code>RESTRICT<\/code>).<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">MySQL and MariaDB<\/h3>\n\n\n\n<p class=\"\">Some DBMSs (such as MySQL and MariaDB) accept the <code>RESTRICT<\/code> and <code>CASCADE<\/code> keywords, but they don&#8217;t do anything. They are provided simply for easier portability between DBMSs.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Oracle<\/h3>\n\n\n\n<p class=\"\">Oracle has a slightly different syntax, <code>CASCADE CONSTRAINTS<\/code>, which drops all referential integrity constraints that refer to primary and unique keys in the dropped table.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">SQL Server<\/h3>\n\n\n\n<p class=\"\">SQL Server doesn&#8217;t support the <code>CASCADE<\/code> or <code>RESTRICT<\/code> keywords. If the table has any foreign key dependencies, you&#8217;ll need to drop them before dropping the table, otherwise you&#8217;ll get an error.<\/p>\n\n\n\n<p class=\"\">However, in SQL Server, you can drop a table even if it&#8217;s referenced by a view or stored procedure. Therefore, you should check for any such references, and explicitly drop them by using <code>DROP VIEW<\/code> or <code>DROP PROCEDURE<\/code>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">SQLite<\/h3>\n\n\n\n<p class=\"\">SQLite doesn&#8217;t support the <code>CASCADE<\/code> or <code>RESTRICT<\/code> keywords. <\/p>\n\n\n\n<p class=\"\">If the target table is referenced by any views, the table will still be dropped (and the view will remain).<\/p>\n\n\n\n<p class=\"\">If the target table is referenced by any foreign keys, the outcome will depend on whether you have <a href=\"https:\/\/database.guide\/how-to-enable-foreign-key-support-in-sqlite\/\" title=\"How to Enable Foreign Key Support in SQLite\">foreign keys enabled<\/a>, and if so, whether there&#8217;s any data in the child table, and if so, whether or not the foreign key is defined with <code>ON DELETE CASCADE<\/code>.<\/p>\n\n\n\n<p class=\"\">If you use SQLite, see <a href=\"https:\/\/database.guide\/sqlite-drop-table\/\" title=\"SQLite DROP TABLE\">SQLite <code>DROP TABLE<\/code><\/a> for an example and discussion of dropping a table that&#8217;s referenced by a foreign key.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Drop Multiple Tables<\/h2>\n\n\n\n<p class=\"\">Some RDBMSs allow you to drop multiple tables from a single <code>DROP TABLE<\/code> statement.<\/p>\n\n\n\n<p class=\"\">Example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DROP TABLE t11, t12;<\/code><\/pre>\n\n\n\n<p class=\"\">The RDBMSs that support this syntax include SQL Server, MySQL, MariaDB, and PostgreSQL. <\/p>\n\n\n\n<p class=\"\">However, if you&#8217;re dropping a table that&#8217;s referenced by a foreign key, and that foreign key prevents it from being dropped, you&#8217;ll need to list the child table before the parent table.<\/p>\n\n\n\n<p class=\"\">For example, if I run the above statement in SQL Server, I get the following error:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Msg 3726, Level 16, State 1, Line 1\nCould not drop object 't11' because it is referenced by a FOREIGN KEY constraint.<\/pre>\n\n\n\n<p class=\"\">In this case, I can simply switch the order of the tables in my <code>DROP TABLE<\/code> statement:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DROP TABLE t12, t11;<\/code><\/pre>\n\n\n\n<p class=\"\">Actually, in this case, I got another error telling me that <code>t12<\/code> doesn&#8217;t exist. <\/p>\n\n\n\n<p class=\"\">Here&#8217;s what I got:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Msg 3701, Level 11, State 5, Line 1\nCannot drop the table 't12', because it does not exist or you do not have permission.<\/pre>\n\n\n\n<p class=\"\">This is because, even though the previous statement couldn&#8217;t drop <code>t11<\/code>, it was in fact successful in dropping <code>t12<\/code>.<\/p>\n\n\n\n<p class=\"\">And just like a comedy of errors, this time it was able to drop <code>t11<\/code> but not <code>t12<\/code>. <\/p>\n\n\n\n<p class=\"\">Regardless, both tables have now been now dropped.<\/p>\n\n\n\n<p class=\"\">But if you get the order correct the first time, you should receive a message like this:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Commands completed successfully.<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">The TEMPORARY Keyword<\/h2>\n\n\n\n<p class=\"\">Some RDBMSs (such as MySQL and MariaDB) accept a <code>TEMPORARY<\/code> keyword.<\/p>\n\n\n\n<p class=\"\">It goes between <code>DROP<\/code> and <code>TABLE<\/code>, like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DROP TEMPORARY TABLE t1;<\/code><\/pre>\n\n\n\n<p class=\"\">Using the <code>TEMPORARY<\/code> keyword will ensure that you don&#8217;t accidentally drop a non-temporary table when attempting to drop a temporary table.<\/p>\n\n\n\n<p class=\"\">The&nbsp;<code>TEMPORARY<\/code>&nbsp;keyword has the following effects:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li class=\"\">The statement drops only\u00a0<code>TEMPORARY<\/code>\u00a0tables.<\/li>\n\n\n\n<li class=\"\">The statement does not cause an implicit commit (using <code>DROP TABLE<\/code> without the <code>TEMPORARY<\/code> keyword automatically commits the current active transaction).<\/li>\n\n\n\n<li class=\"\">No access rights are checked. A\u00a0<code>TEMPORARY<\/code>\u00a0table is visible only with the session that created it, so no check is necessary.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">The Purge Clause<\/h2>\n\n\n\n<p class=\"\">Oracle has an optional <code>PURGE<\/code> clause, which you can use if you want to drop the table and release the space associated with it in a single step. If you specify&nbsp;<code>PURGE<\/code>, then the database does not place the table and its dependent objects into the recycle bin. <\/p>\n\n\n\n<p class=\"\">This&nbsp;is equivalent to first dropping the table and then purging it from the recycle bin, but it lets you save one step in the process.<\/p>\n\n\n\n<p class=\"\">Note that if you specify <code>PURGE<\/code>, you won&#8217;t be able to recover the table.<\/p>\n\n\n\n<p class=\"\">If you don&#8217;t specify&nbsp;<code>PURGE<\/code>, the&nbsp;<code>DROP<\/code>&nbsp;<code>TABLE<\/code>&nbsp;statement does not result in space being released back to the tablespace for use by other objects, and the space continues to count toward the user&#8217;s space quota.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In SQL, if you want to remove a table from a database, you need to use the DROP TABLE statement. That destroys the table and all its data.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[10,48,26],"class_list":["post-12143","post","type-post","status-publish","format-standard","hentry","category-sql","tag-how-to","tag-sql","tag-tables"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/12143","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=12143"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/12143\/revisions"}],"predecessor-version":[{"id":46207,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/12143\/revisions\/46207"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=12143"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=12143"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=12143"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}