SQLite Create Table If Not Exists: Conditional Table Creation
Tags: sql, sqlserver, database
Published: 2023-10-11 07:07:53

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](https://sqldocs.org/sqlite/sqlite-create-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", "john@example.com");
INSERT INTO users (name, email) VALUES ("Jane Smith", "jane@email.com");
```

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    john@example.com
2          Jane Smith  jane@email.com
```


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.