WL#3129: Consistent clauses in CREATE and DROP

There are clauses or options that apply to   
some CREATE and DROP statements, but not all.   
We could be more consistent.   
   
The inconsistency   
-----------------   

We're talking about IF EXISTS / IF NOT EXISTS   
in CREATE and DROP, and about the number of   
objects that we can drop in one statement.   

Here, have a chart:   
   
Object      CREATE IF NOT EXISTS DROP IF EXISTS DROP MANY   
------      -------------------- -------------- ---------   
   
DATABASE    yes                  yes            -   
EVENT       yes                  yes            -   
FUNCTION    -                    yes            -   
INDEX       -                    -              -   
PROCEDURE   -                    yes            -   
TABLE       yes                  yes            yes   
TRIGGER     -                    yes            -   
USER        -                    -              yes   
VIEW        -                    yes            yes   
SERVER      -                    yes            - (as tested 2007-01-10)

A "yes" in the "CREATE IF NOT EXISTS" column means   
CREATE object_type object_name IF NOT EXISTS is legal, e.g.   
"CREATE DATABASE d IF NOT EXISTS ...".   
   
A "yes" in the "DROP IF EXISTS" column means   
DROP object_type object_name IF EXISTS is legal, e.g.   
"DROP EVENT IF EXISTS e"   
   
A "yes" in the "DROP MANY" column means   
DROP object_type object_name [, object_name ...] is legal, e.g.   
"DROP TABLE t1,t2,t3"   
   
Other DBMSs   
-----------   
   
Other DBMSs also lack consistency, although they're   
unanimous about not using IF EXISTS / IF NOT EXISTS.   
   
Here, have another chart:   
   
           CREATE        CREATE        DROP      DROP   
           IF NOT EXISTS OR REPLACE    IF EXISTS many   
           ------------- ------------- --------- ------   
Oracle     never         sometimes [1] never     never   
SQL Server never         never         never     usually [2]   
DB2        never         never         never     never   
PostgreSQL never         sometimes [3] never     sometimes [4]   
   
[1] Oracle allows CREATE OR REPLACE for functions, procedures,   
    triggers, views   
[2] SQL Server allows "DROP name [,name...] for most objects   
    but not users or roles   
[3] PostgreSQL allows CREATE OR REPLACE for functions and views   
[4] PostgreSQL allows "DROP name [,name...] for tables, users,   
    views and roles   

   
Opinions   
--------   
   
Re "CREATE IF [NOT] EXISTS":   
Sergei Golubchik says it "should be supported everywhere".   
Konstantin Osipov says the "intent" is to support it for triggers.   
   
Re "DROP IF EXISTS":   
Trudy Pelzer says "I like consistency too".   
   
Re "DROP MANY":   
Sergei Golubchik says it should not be encouraged "because   
of unclear semantics".   
Konstantin Osipov says: "although it's a very convenient syntax,   
it's inherently unsafe. I've no strong opinion whether we should   
support it consistently long-term, especially in the light that   
all DDL operations are non-transactional."   
   
Kristian Koehntopp says the whole thing is "very unsystematic"   
and we should have a unified way to handle CREATE, DROP,   
ALTER, and SHOW.   

Carsten Pedersen asked about "TRUNCATE TABLE IF EXISTS" in
an old dev-private thread.

A commenter on BUG#15287 suggested that it would be better to
support dynamic compound statements, see WL#3696.

Materialized Views
------------------

Peter proposed CREATE [OR REPLACE] MATERIALIZED VIEW for
WL#2866. During a meeting in Stockholm, it was decided:
no, but consider allowing CREATE [or REPLACE] MATERIALIZED
VIEW for WL#3129.

References   
----------   
   
dev-private thread "Create if not exists, Drop if exists, Drop many"   
https://intranet.mysql.com/secure/mailarchive/mail.php?folder=4&mail=12907   
   
dev-private thread "MySQL - a very unsystematic API"   
https://intranet.mysql.com/secure/mailarchive/mail.php?folder=4&mail=12963   
 
BUG#2935 "index-level comments" 
http://bugs.mysql.com/bug.php?id=2935 
 
BUG#15287 "IF EXIST / IF NOT EXIST"   
http://bugs.mysql.com/bug.php?id=15287   
 
WL#605 "CREATE [OR REPLACE] support"  
https://intranet.mysql.com/worklog/Server-RawIdeaBin/?tid=605

BUG#18466 add REPLACE to CREATE FUNCTION, PROCEDURE, TRIGGER

BUG#19166 DROP USER IF EXISTS

BUG#23543 add feature "drop index if exists"

BUG#31303 Drop tablespace should support IF EXISTS

BUG#4754 'if exists' syntax for rename table

BUG#51318 implement "if exists" for DROP LOGFILE GROUP, DROP TABLESPACE commands

BUG#61890 truncate if exists