In SQLite, you can use the IF NOT EXISTS clause of the CREATE TABLE statement to check whether or not a table or view of the same name already exists in the database before creating it.

Creating a table without this clause would normally result in an error if a table of the same name already existed in the database. But when using the IF NOT EXISTS clause, the statement has no effect if a table already exists with the same name.

Example

Here’s an example to demonstrate:

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 that case, the table will only be created if there isn’t already a table or view called t1.

Check that the Table Now Exists

We can query the sqlite_schema table to check to see if the table now exists:

SELECT EXISTS (
    SELECT 
        name
    FROM 
        sqlite_schema 
    WHERE 
        type='table' AND 
        name='t1'
    );

Result:

1

In this case I get 1, which means that the table does exist.

Try to Create the Table Again

If we attempt to create that table again:

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

We don’t get an error:

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

We get nothing.

Without the IF NOT EXISTS Clause

Here’s what happens when we don’t use the IF NOT EXISTS clause when trying to create a table that already exists:

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

This time we get an error:

Error: table t1 already exists

Note that the IF NOT EXISTS clause does not check the table structure/definition. It simply checks that there’s no existing table or view with the same name that we’re trying to give to the table that we’re creating.

Put another way, just because a table of that name already exists, it doesn’t mean it has the correct definition.

Also, an error is still returned if the table cannot be created because of an existing index, even if the IF NOT EXISTS clause is specified.