168

Can someone tell me if there is any difference between

DROP IF EXISTS [TABLE_NAME]

DROP [TABLE_NAME]

I am asking this because I am using JDBC template in my MVC web application. If I use DROP [TABLE_NAME] the error said that Table exist. And if I use DROP IF EXISTS [TABLE_NAME] it says bad SQL grammar. Can some one help?

lospejos
  • 1,888
  • 3
  • 17
  • 31
AbdulAziz
  • 4,948
  • 12
  • 51
  • 75

5 Answers5

304

Standard SQL syntax is

DROP TABLE table_name;

IF EXISTS is not standard; different platforms might support it with different syntax, or not support it at all. In PostgreSQL, the syntax is

DROP TABLE IF EXISTS table_name;

The first one will throw an error if the table doesn't exist, or if other database objects depend on it. Most often, the other database objects will be foreign key references, but there may be others, too. (Views, for example.) The second will not throw an error if the table doesn't exist, but it will still throw an error if other database objects depend on it.

To drop a table, and all the other objects that depend on it, use one of these.

DROP TABLE table_name CASCADE;
DROP TABLE IF EXISTS table_name CASCADE;

Use CASCADE with great care.

Mike Sherrill 'Cat Recall'
  • 82,047
  • 16
  • 110
  • 161
  • 6
    Just thought I'd mention that it would be a **really** **good** idea for one to use `CASCADE` within a transaction block (`BEGIN` ... `COMMIT`). This way it is clear how the database will be affected _before_ potentially clobbering a bunch of data you may not have wanted to. – jbowman Apr 21 '15 at 17:57
  • 3
    DROP IF EXISTS (without CASCADE) is also added in SQL Server 2016. See http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016.aspx – Jovan MSFT Nov 03 '15 at 12:59
35

It is not what is asked directly. But looking for how to do drop tables properly, I stumbled over this question, as I guess many others do too.

From SQL Server 2016+ you can use

DROP TABLE IF EXISTS dbo.Table

For SQL Server <2016 what I do is the following for a permanent table

IF OBJECT_ID('dbo.Table', 'U') IS NOT NULL 
  DROP TABLE dbo.Table;

Or this, for a temporary table

IF OBJECT_ID('tempdb.dbo.#T', 'U') IS NOT NULL
  DROP TABLE #T;
Squazz
  • 3,463
  • 5
  • 33
  • 50
17

You forgot the table in your syntax:

drop table [table_name]

which drops a table.

Using

drop table if exists [table_name]

checks if the table exists before dropping it.
If it exists, it gets dropped.
If not, no error will be thrown and no action be taken.

juergen d
  • 186,950
  • 30
  • 261
  • 325
4
DROP TABLE IF EXISTS [table_name]

it first checks if the table exists, if it does it deletes the table while

DROP TABLE [table_name]

it deletes without checking, so if it doesn't exist it exits with an error

Flakron Bytyqi
  • 3,206
  • 16
  • 20
3

If no table with such name exists, DROP fails with error while DROP IF EXISTS just does nothing.

This is useful if you create/modifi your database with a script; this way you do not have to ensure manually that previous versions of the table are deleted. You just do a DROP IF EXISTS and forget about it.

Of course, your current DB engine may not support this option, it is hard to tell more about the error with the information you provide.

SJuan76
  • 23,682
  • 6
  • 41
  • 79
  • It may also be worth mentioning that some RDBMSs (PostgreSQL in particular) throw a warning if you try `drop some_table if exists;` and the table `some_table` doesn't exist. –  Mar 05 '12 at 11:31
  • Ah, and of course juegen d and Flakron are right. `DROP` needs that you specifiy the type of object (`TABLE` in this case) – SJuan76 Mar 05 '12 at 11:32