IF NOT EXISTS / IF EXISTS (CONDITIONAL DDL)DDL
Buy Me A Coffee

Modifiers on DDL statements that suppress errors when an object already exists (or does not exist), enabling idempotent schema scripts.

Conditional DDL that only executes the command if the object does not exist (IF NOT EXISTS) or does exist (IF EXISTS). Prevents errors when running scripts multiple times and enables idempotent schema deployments.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 5.0 IF NOT EXISTS on CREATE TABLE, CREATE INDEX, CREATE DATABASE, CREATE SCHEMA. IF EXISTS on DROP TABLE, DROP INDEX, ALTER TABLE DROP COLUMN (8.0+).
PostgreSQL ✓ Supported 9.1 IF NOT EXISTS added for CREATE TABLE in 9.1, CREATE INDEX in 9.5, CREATE SCHEMA in 9.3. IF EXISTS on DROP widely available. ALTER TABLE ... DROP COLUMN IF EXISTS since 9.0.
SQL Server ✓ Supported 2016 DROP TABLE IF EXISTS, DROP INDEX IF EXISTS added in 2016. CREATE TABLE IF NOT EXISTS is not supported — use IF NOT EXISTS (SELECT 1 FROM sys.tables ...) workaround or OBJECT_ID() check.
Oracle ✓ Supported 23c IF EXISTS and IF NOT EXISTS on CREATE/DROP TABLE/INDEX added in Oracle 23c. On earlier versions, use PL/SQL exception handling (WHEN OBJ_ALREADY_EXISTS / WHEN NO_DATA_FOUND) as a workaround.
SQLite ✓ Supported 3.0.0 CREATE TABLE IF NOT EXISTS and DROP TABLE IF EXISTS are fully supported. CREATE INDEX IF NOT EXISTS since 3.3.7.

Details

Conditional DDL is essential for migration scripts and deployment idempotency. PostgreSQL and MySQL have the most complete coverage across object types. SQL Server was the laggard — DROP...IF EXISTS arrived in 2016, but CREATE TABLE IF NOT EXISTS still doesn't exist natively, making the OBJECT_ID() guard the standard idiom. Oracle 23c finally added the syntax but it's very recent, so any code targeting older Oracle versions needs the PL/SQL exception-swallowing pattern. SQLite's support is consistent and well-established. A subtle trap: CREATE OR REPLACE (available in PostgreSQL, Oracle, and partially MySQL) is a related but distinct concept — it replaces an existing object rather than silently skipping creation.

Standard Syntax

CREATE TABLE IF NOT EXISTS t (id INT); DROP TABLE IF EXISTS t;

Version Support

MySQL: Since 5.0 PostgreSQL: Since 9.1 SQL Server: Since 2016 Oracle: Since 23c SQLite: Since 3.0.0

Per-Database Syntax & Notes

MySQL

IF NOT EXISTS on CREATE TABLE, CREATE INDEX, CREATE DATABASE, CREATE SCHEMA. IF EXISTS on DROP TABLE, DROP INDEX, ALTER TABLE DROP COLUMN (8.0+).

CREATE TABLE IF NOT EXISTS orders (id INT PRIMARY KEY); DROP TABLE IF EXISTS orders;

PostgreSQL

IF NOT EXISTS added for CREATE TABLE in 9.1, CREATE INDEX in 9.5, CREATE SCHEMA in 9.3. IF EXISTS on DROP widely available. ALTER TABLE ... DROP COLUMN IF EXISTS since 9.0.

CREATE TABLE IF NOT EXISTS orders (id INT PRIMARY KEY); CREATE INDEX IF NOT EXISTS idx_orders_id ON orders(id); DROP TABLE IF EXISTS orders;

SQL Server

DROP TABLE IF EXISTS, DROP INDEX IF EXISTS added in 2016. CREATE TABLE IF NOT EXISTS is not supported — use IF NOT EXISTS (SELECT 1 FROM sys.tables ...) workaround or OBJECT_ID() check.

DROP TABLE IF EXISTS dbo.orders; -- Create workaround: IF OBJECT_ID('dbo.orders','U') IS NULL CREATE TABLE dbo.orders (id INT PRIMARY KEY);

Oracle

IF EXISTS and IF NOT EXISTS on CREATE/DROP TABLE/INDEX added in Oracle 23c. On earlier versions, use PL/SQL exception handling (WHEN OBJ_ALREADY_EXISTS / WHEN NO_DATA_FOUND) as a workaround.

-- Oracle 23c+ CREATE TABLE IF NOT EXISTS orders (id NUMBER PRIMARY KEY); DROP TABLE IF EXISTS orders; -- Pre-23c workaround: BEGIN EXECUTE IMMEDIATE 'CREATE TABLE orders (id NUMBER)'; EXCEPTION WHEN OTHERS THEN NULL; END;

SQLite

CREATE TABLE IF NOT EXISTS and DROP TABLE IF EXISTS are fully supported. CREATE INDEX IF NOT EXISTS since 3.3.7.

CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY); DROP TABLE IF EXISTS orders;