DROP IF EXISTS VS DROP?

sql drop-table
AbdulAziz picture AbdulAziz · Mar 5, 2012 · Viewed 407.5k times · Source

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?

Answer

Mike Sherrill 'Cat Recall' picture Mike Sherrill 'Cat Recall' · Mar 5, 2012

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.

Related questions

Drop multiple tables in one shot in mysql

How to drop multiple tables from one single database at one command. something like, > use test; > drop table a,b,c; where a,b,c are the tables from database test.

Read More
Can't drop table: A foreign key constraint fails

In MySQL I want to drop a table. I tried a lot things but I keep getting the error that the table named bericht can't be dropped. This is the error I'm getting: #1217 - Cannot delete or update a parent …

Read More
error in sqlite "DROP TABLE IF EXISTS" android

so i have a problem in my DBAdapter class its just crushes when i try to open the database: from the LogCat i guess the problem is in the onUpgrade function: public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { …

Read More