CREATE TABLE IF NOT EXISTS statement in SQLite

Question

I have a WPF application where I access a SQLite database via ADO.NET (http://adodotnetsqlite.sourceforge.net/). So far everything works fine, but when I try to execute the following SQL:

 sqlite_cmd.CommandText = "CREATE TABLE IF NOT EXISTS notes (id integer primary key, text varchar(100));";
 sqlite_cmd.ExecuteNonQuery();

I get the following exception:

An exception of type 'Finisar.SQLite.SQLiteException' occurred in SQLite.NET.dll but was not handled in user code.
Additional information: near "NOT": syntax error

When I remove the IF NOT EXISTS part it works fine, but I want to create the table only if it is not already there. Is there anything I'm doing wrong?

Potentially related to http://stackoverflow.com/q/1601151/23902? Oddly, though, that question says that SQLite 3.3+ supports `IF NOT EXISTS`... — Jeff, Mar 13 '15 at 15:56
Check out http://stackoverflow.com/questions/3716443/creating-an-sqlite-table-only-if-it-doesnt-already-exist which runs into the same issue. — LinkBerest, Mar 13 '15 at 15:58

score 5 · Accepted Answer · edited May 23 '17 at 11:45

This question has some answers which may be helpful. From that question, however, this answer suggests that SQLite 3.3 and above support IF NOT EXISTS.

Based on that question's answers, you could try selecting the COUNT of tables named 'notes' using this (slightly modified) query:

SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='notes';

You can then test the result of that query. If there were 0 results, create the table. Otherwise, don't create the table.

CREATE TABLE IF NOT EXISTS statement in SQLite

1 Answers1