SQL Reference ยท Data Definition
SQL IF EXISTS and IF NOT EXISTS
Reference for IF EXISTS and IF NOT EXISTS in SQL DDL: write scripts that can run twice, which statements accept them and where dialects differ.
Schema scripts usually run more than once: in test setups, on every deploy, after a failed attempt. A plain CREATE TABLE fails if the table is already there and a plain DROP TABLE fails if it is not. The IF NOT EXISTS and IF EXISTS clauses turn those errors into quiet no-ops.
They check the name only. CREATE TABLE IF NOT EXISTS does not compare columns or update the structure, so an old table with a different shape stays as it is.
CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT); DROP TABLE IF EXISTS notes;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
CREATE TABLE IF NOT EXISTS name (...); CREATE INDEX IF NOT EXISTS name ON table (...); CREATE VIEW IF NOT EXISTS name AS ...; DROP TABLE IF EXISTS name; DROP INDEX IF EXISTS name; DROP VIEW IF EXISTS name;
| Clause | Effect |
|---|---|
IF NOT EXISTS |
Skip the creation if an object with that name exists. |
IF EXISTS |
Skip the drop if there is no such object. |
Dialect differences
| Dialect | Notes |
|---|---|
| SQLite | Supported on CREATE and DROP of tables, indexes, views, triggers. ALTER TABLE ADD COLUMN has no such clause. |
| PostgreSQL | Supported broadly, including ADD COLUMN IF NOT EXISTS and DROP COLUMN IF EXISTS; emits a notice when skipping. |
| MySQL | IF NOT EXISTS / IF EXISTS on most objects (a warning is raised, see SHOW WARNINGS); column-level clauses exist only in MariaDB. |
| SQL Server | DROP ... IF EXISTS since 2016; there is no IF NOT EXISTS on CREATE TABLE: test with IF OBJECT_ID('t') IS NULL. |
| Oracle | Both clauses arrived in 23ai; earlier versions catch the error in PL/SQL. |
-- SQL Server IF OBJECT_ID('dbo.notes', 'U') IS NULL CREATE TABLE dbo.notes (id INT PRIMARY KEY, body NVARCHAR(200)); -- PostgreSQL ALTER TABLE notes ADD COLUMN IF NOT EXISTS created_at timestamptz;
Usage notes
- Not a migration tool. If the structure differs, nothing is changed; use versioned migrations that alter tables deliberately.
- Hidden mistakes: a typo in a name with
DROP ... IF EXISTSsucceeds silently, so you may think something was removed that never was. - Race conditions: on a busy server two sessions can both pass the check; the database usually still keeps the second from creating a duplicate.
CREATE ... IF NOT EXISTSplus data: the insert after it will run each time, so make it idempotent (seeUPSERTorINSERT OR IGNORE).- Not to be confused with the
EXISTSoperator used in queries.
Related
CREATE TABLE,DROP TABLE,CREATE INDEX,UPSERT,EXISTS- Lessons: ALTER and DROP, Create table