SQLite Create Table If Not Exists: Conditional Table Creation

Working with SQLite? You've probably tried creating a table only to be greeted by an annoying error telling you the table already exists! SQLite yelling at you for trying to create duplicate tables can quickly become frustrating. But luckily, SQLite provides a handy way to avoid these errors by using "CREATE TABLE IF NOT EXISTS."

This simple clause allows you to attempt to create a table but not fail if it already exists. It's a smooth, conditional way to create SQLite tables that make your life easier. Let me walk through some examples to show you how it works!

Creating a Basic Table

First, let’s look at creating a normal table. This SQL statement will create a new table called users:

<pre class="wp-block-code">```

sql
CREATE TABLE users (
  id INTEGER PRIMARY KEY, 
  name TEXT,
  email TEXT
);

Easy enough! But now, if we try to create that users table again...

Uh oh! SQLite yells at us with an error saying the table already exists. Bummer.

This is where CREATE TABLE IF NOT EXISTS comes to the rescue!

Using IF NOT EXISTS

To avoid errors from creating duplicate tables, we can use the “IF NOT EXISTS” clause:

<pre class="wp-block-code">

```sql
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY, 
  name TEXT,
  email TEXT 
);

Now if the users table doesn’t exist; it will be created. But if it already exists, no error will occur. This makes your SQL code more robust.

Let’s test this out. First, we’ll create the table:

<pre class="wp-block-code">```

sql
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY,
  name TEXT, 
  email TEXT
);

This will create the users table since it doesn’t exist yet.

Now let’s try creating it again:

<pre class="wp-block-code">

```sql
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY, 
  name TEXT,
  email TEXT
);

No errors! The table was not created again because it already existed.

Example with Inserts

To see a more realistic example, let’s insert some data after conditionally creating the table:

<pre class="wp-block-code">```

sql
CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY,
  name TEXT,
  email TEXT 
);

INSERT INTO users (name, email) VALUES ("John Doe", "[e-mail]");
INSERT INTO users (name, email) VALUES ("Jane Smith", "[e-mail]");

This will create the users table if needed, then insert the two new rows. We can query to see the inserted data:

<pre class="wp-block-code">

```sql
SELECT * FROM users;

Which prints:

<pre class="wp-block-code">```

sql
id         name        email
----------  ----------  ----------------
1          John Doe    [e-mail]
2          Jane Smith  [e-mail]

The key is that this will succeed whether or not the users table already exists.

Summary

The “CREATE TABLE IF NOT EXISTS” syntax in SQLite provides a convenient way to create tables conditionally. This avoids errors from duplicate table creation and makes your SQL code more robust and reusable.