[
https://issues.apache.org/jira/browse/DERBY-4842?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16265474#comment-16265474
]
Rick Hillegas commented on DERBY-4842:
--------------------------------------
There is also the CREATE OR REPLACE syntax, which is supported to varying
extents by DB2
(https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_71/sqlp/rbafyreplacetable.htm),
Oracle (http://www.dba-oracle.com/t_create_or_replace_procedure.htm), MariaDB
(https://mariadb.com/kb/en/library/create-table/), Snowflake
(https://mariadb.com/kb/en/library/create-table/), Kognitio
(https://kognitio.com/documentation/latest/sqlref/objects/object-view-replace.html),
Sybase
(http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00641.1600/doc/html/car1372019568037.html),
and Vertica
(https://my.vertica.com/docs/7.0.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATEVIEW.htm).
CREATE OR REPLACE is more powerful. If the object exists, then it is replaced
with a new definition. However, at first blush, CREATE OR REPLACE appears to be
considerably harder to implement. That is because it would be tricky to
implement the required dependency tracking.
The check for the existence of the schema object happens at execution time in
the Create*ConstantAction classes. I think that implementing CREATE IF NOT
EXISTS would be fairly straightforward. A little thought would have to be given
to designing an object-neutral way to pass the IF NOT EXISTS directive from the
parser all the way through to the ConstantActions. The syntax could be rolled
in, one schema object at a time.
I think that CREATE IF NOT EXISTS would be a useful extension to the Standard
and I would support adding it to Derby.
> Support "IF [NOT] EXISTS" in CREATE TABLE and "IF EXISTS" in DROP TABLE
> -------------------------------------------------------------------------
>
> Key: DERBY-4842
> URL: https://issues.apache.org/jira/browse/DERBY-4842
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Adrian Tarau
> Labels: derby_triage10_10
>
> It would be really great if Derby will support these additions similar with
> what MySQL has.
> Also a way to disable constraints when dropping the whole database, so you
> would not be forced to put DROPs in such an order that constraints will not
> fail.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
