DDL meets IF [NOT] EXISTS

Oracle has CREATE OR REPLACE PROCEDURE, now so does SQL Anywhere 11.0.1.

...plus these:

CREATE OR REPLACE FUNCTION f ...
CREATE OR REPLACE TRIGGER t ...
CREATE OR REPLACE VIEW v ...

I've never understood why Oracle doesn't have CREATE OR REPLACE TABLE, and for some inexplicable reason, neither does SQL Anywhere 11.0.1 (if it's some kinda AnsiNazi rule, just shoot me now).

What SQL 11.0.1 Anywhere does have (and Oracle 11g doesn't) is

DROP TABLE IF EXISTS t

...plus these:

DROP EVENT IF EXISTS e
DROP FUNCTION IF EXISTS f
DROP MATERIALIZED VIEW IF EXISTS m
DROP PROCEDURE IF EXISTS p
DROP TRIGGER IF EXISTS t
DROP VIEW IF EXISTS v

SQL Anywhere 11.0.1 also has this:

CREATE TABLE IF NOT EXISTS t ...

Hmmmm... let me think... the d00dz in Waterloo don't do anything without a good reason... after all, their motto has always been

WATCOM does things the way they should be done!

Here is Scenario 1: You want make sure a table exists, but if it already exists you want to leave it alone:

CREATE TABLE IF NOT EXISTS t (
   pkey INTEGER NOT NULL PRIMARY KEY,
   data INTEGER NOT NULL );

Here's Scenario 2: You want to create a table from scratch, regardless of whether or not it already exists:

DROP TABLE IF EXISTS t;

CREATE TABLE t (
   pkey INTEGER NOT NULL PRIMARY KEY,
   data INTEGER NOT NULL );

Now it makes sense; CREATE OR REPLACE TABLE would only do Scenario 2... no need to shoot me :)

---

Download "Upgrade of 11.0.0 to 11.0.1"
What's new in version 11.0.1
New Editions in SQL Anywhere 11.0.1
SQL Anywhere 11.0.1 FAQs
SQL Anywhere 11.0.1 Editions