SQL: DROP, CREATE,… How to avoid errors?

In SQL we often want to create or drop an object (TABLE, VIEW, SEQUENCE, INDEX, SYNONYM, DATABASE, USER, SCHEMA,….)

Now we do not know if this thing exists or not. We would like to write scripts, that work in either case.

So something like

CREATE TABLE IF NOT EXISTS XYZ (...);

or

DROP TABLE IF EXISTS XYZ;
CREATE TABLE XYZ (...);

would be desirable.

This seems to be quite hard, depending on the database.

Some databases support the „IF EXISTS“ or even the slightly less useful „IF NOT EXISTS“ pattern for some of the CREATE and DROP statements:

COMMAND	PostgreSQL	MS-SQL-Server	Oracle	DB2	MySQL/MariaDB
DROP DATABASE IF EXISTS	x	x	-	-	x
DROP FUNCTION IF EXISTS	x	x	-	x	x
DROP INDEX IF EXISTS	x	x	-	x	x
DROP MATERIALIZED VIEW IF EXISTS	x	Materialized view not found in documentation	-	Materialized view not found in documentation	no materialized views supported
DROP ROLE IF EXISTS	x	x	-	x	x
DROP SCHEMA IF EXISTS	x	x	no DROP SCHEMA, Oracle uses User to express the concept of a Schema	? (no DROP SCHEMA found in documentation)	Schema not supported.
DROP SEQUENCE IF EXISTS	x	x	-	x	x
DROP TABLE IF EXISTS	x	x	-	x	x
DROP TABLESPACE IF EXISTS	x	Not found in documentation	-	? (no DROP TABLESPACE found in documentation)	TABLESPACE not supported
DROP TRIGGER IF EXISTS	x	x	-	x	x
DROP USER IF EXISTS	x	x	-	-	x
DROP VIEW IF EXISTS	x	x	-	x	x
CREATE OR REPLACE DATABASE	-	-	-	-	x
CREATE DATABASE IF NOT EXISTS	-	-	-	x	x
CREATE FUNCTION IF NOT EXISTS	-	-	-	x	-
CREATE OR REPLACE FUNCTION	x	x (CREATE OR ALTER FUNCTION)	x	-	x
CREATE OR REPLACE INDEX	-	-	-	-	x
CREATE INDEX IF NOT EXISTS	x	-	-	x	x
CREATE MATERIALIZED VIEW IF NOT EXISTS	x	Materialized view not found in documentation	-	Materialized view not found in documentation	no materialized views supported
CREATE OR REPLACE ROLE	-	-	-	-	x
CREATE ROLE IF NOT EXISTS	-	-	-	x	x
CREATE SCHEMA IF NOT EXISTS	x	-	Oracle ties the schema closely to a user. So slightly different meaning of CREATE SCHEMA...	Oracle ties the schema closely to a user. So slightly different meaning of CREATE SCHEMA...	Schema not supported.
CREATE OR REPLACE SEQUENCE	-	-	-	-	x
CREATE SEQUENCE IF NOT EXISTS	x	-	-	x	x
CREATE OR REPLACE TABLE	-	-	-	-	x
CREATE TABLE IF NOT EXISTS	x	-	-	x	x
CREATE TABLESPACE IF NOT EXISTS	-	Not found in documentation	-	TABLESPACE apparently not supported	TABLESPACE not supported
CREATE OR REPLACE TRIGGER	-	x (CREATE OR ALTER TRIGGER)	x	-	x
CREATE TRIGGER IF NOT EXISTS	-	-	-	x	x
CREATE OR REPLACE USER	-	-	-	-	x
CREATE USER IF NOT EXISTS	-	-	-	-	x
CREATE OR REPLACE VIEW	x	x (CREATE OR ALTER VIEW)	x	-	x
CREATE VIEW IF NOT EXISTS	-	-	-	x	x

Usually we can happily ignore the missing „IF (NOT) EXISTS“ conditions and just DROP the object and then create it. By ignoring the error message this works.

But increasingly we want to manage the database with tools that run scripts automatically and cannot distinguish as well as a DBA between „good error messages“ and „bad error messages“.

So we should find our ways around this.

The trick is to use the procedural extension language of SQL (like PL/SQL for Oracle, because in case of Oracle we need it most often). Then we write a block and find by a select in the data dictionary if the object we are creating or dropping exists. Then we put the select result into a variable and put an IF-condition around our CREATE or DROP statement. This approach can also be useful for dropping all objects of a certain type that fullfill some SQL-query-accessible condition.

DECLARE
   CNT INT;
BEGIN
   SELECT COUNT(*) INTO CNT 
     FROM USER_TABLES 
     WHERE TABLE_NAME = 'MY_TABLE';
   IF CNT >= 1 THEN
      EXECUTE IMMEDIATE 'DROP TABLE MY_TABLE';
   END IF;
END;
/

Or to catch the kind of exception that we want to ignore (example for Oracle):

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE MY_TABLE';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/

It is a bit tricky to find the reference pages for the SQL dialects of the different DB products. I used these:

• PostgreSQL 10
• MS-SQL-Server / T-SQL
• Oracle 12.1
• DB2 12.1
• MariaDB

MariaDB is a fork of mySQL that was created, when mySQL came to Oracle. I recommend it as the successor of mySQL, if you are into mySQL-like databases. And I strongly recommend looking into PostgreSQL, if you intend to use a free/opensource database, because it is the same price and just has richer features. Generally all five databases are good and have their areas of strength and their weaknesses. I won’t go deeper into this in this article…