When creating a table with SQL, we can specify that the table is only to be created if it doesn’t already exist. Doing this prevents any errors from occurring in the event that the table already exists.

Here are some examples of doing this in various RDBMSs.

The CREATE TABLE IF NOT EXISTS Statement

Many RDBMSs support the IF NOT EXISTS clause of the CREATE TABLE statement which makes it easy to create a table only when it doesn’t already exist.

Example:

CREATE TABLE IF NOT EXISTS t1 (
  c1 INT,
  c2 VARCHAR(10)
);

Here, t1 is the table name, and everything between the parentheses is the table definition (i.e. columns, etc).

In this case, the table will only be created if there isn’t already one called t1.

The CREATE TABLE IF NOT EXISTS statement can be used with RDBMSs such as MySQL, MariaDB, PostgreSQL, and SQLite.

The CREATE TABLE IF NOT EXISTS statement was introduced in Oracle Database 23c, but it still isn’t supported by SQL Server (at least not the latest version at the time of writing – SQL Server 2022) or Oracle 21c and lower, but we can use one of the methods below.

SQL Server

SQL Server doesn’t support the CREATE TABLE IF NOT EXISTS statement, so we need to use another option.

One option is to use the OBJECT_ID() function to check for the existence of the table before we try to create it:

IF OBJECT_ID(N'dbo.t1', N'U') IS NULL
CREATE TABLE dbo.t1 (
    c1 int,
    c2 varchar(10)
    );
GO

In this example we’re checking the object ID of a dbo.t1 table.

The second argument specifies the type of object we’re looking for. In this case we use U, which is for “user defined table”.

The OBJECT_ID() returns the database object identification number of a schema-scoped object. If the object doesn’t exist, or if we don’t have access to it, the function returns NULL. Therefore, we can check for a NULL value, and only create the table if this function returns NULL.

This is one of at least two methods for creating a table only if it doesn’t exist in SQL Server.

Oracle

The CREATE TABLE IF NOT EXISTS syntax was introduced in Oracle Database 23c. Prior to this version, the CREATE TABLE IF NOT EXISTS statement isn’t supported. So when using Oracle 21c or lower, we also need to use a different method.

One option is to check the DBA_TABLES data dictionary view:

DECLARE
tbl_count number;
sql_stmt long;

BEGIN
    SELECT COUNT(*) INTO tbl_count 
    FROM dba_tables
    WHERE owner = 'HR'
    AND table_name = 'T1';

    IF(tbl_count <= 0)
        THEN
        sql_stmt:='
        CREATE TABLE T1 (
            c1 number(6,0),
            c2 varchar2(10)
        )';
        EXECUTE IMMEDIATE sql_stmt;
    END IF;
END;

Here, we checked the DBA_TABLES data dictionary view to see if the table already exists. We then only run the CREATE TABLE statement if it doesn’t already exist.

This is one of at least two ways to create a table only if it doesn’t exist in Oracle.