Many RDBMSs implement IF EXISTS
and IF NOT EXISTS
syntax modifiers that can be used with DDL object creation, modification, and deletion, such as CREATE TABLE
and DROP TABLE
statements, to name just a couple.
These syntax modifiers allow us to run such statements without getting an error in the event that the object already exists (if we’re trying to create it or modify it) or doesn’t exist (if we’re trying to drop it).
Until recently, the IF EXISTS
and IF NOT EXISTS
syntax modifiers were unavailable in Oracle Database. In Oracle, we’d need to do a bit of extra work if we wanted to avoid such errors during DDL object creation, modification, and deletion.
But Oracle Database 23c changes this. The IF EXISTS
and IF NOT EXISTS
syntax modifiers were introduced in Oracle Database 23c.
The IF EXISTS
Modifier
We can use the IF EXISTS
modifier when we try to drop an object.
Example:
DROP TABLE IF EXISTS t1;
That statement drops a table called t1
if it exists. The IF EXISTS
modifier ensures that we don’t get an error if the table doesn’t exist.
The IF NOT EXISTS
Modifier
We can use the IF NOT EXISTS
modifier when we try to create an object.
Example:
CREATE TABLE IF NOT EXISTS t1 (
c1 number(6,0),
c2 varchar2(10)
);
That statement creates a table called t1
if it doesn’t already exist. The IF NOT EXISTS
modifier ensures that we don’t get an error if the table already exists.
As mentioned, prior to Oracle Database 23c, we had to do a bit of extra work if we wanted to check for the existence/non-existence of the object before performing such actions.
For example, here are some DROP TABLE IF EXISTS
Alternatives for Oracle and here are 3 Ways to Create a Table if it Doesn’t Already Exist in Oracle (which includes the above example).