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 stackoverflow.com/q/1601151/23902? Oddly, though, that question says that SQLite 3.3+ supports IF NOT EXISTS... — Jeff
– Jeff, Commented Mar 13, 2015 at 15:56
Check out stackoverflow.com/questions/3716443/… which runs into the same issue. — LinkBerest - SO sold our work
– LinkBerest - SO sold our work, Commented Mar 13, 2015 at 15:58

Community · Accepted Answer · 2017-05-23 11:45:33Z

5

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.

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Mar 13, 2015 at 16:01

Jeff

2,8693 gold badges44 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Luis Ramirez De La Rosa · Accepted Answer · 2025-06-05 22:15:15Z

1

I don't know how it runs with or without "IF NOT EXISTS" because that's not cause of the error. The CREATE statement use TEXT as the name of the field and TEXT is a field type. You need to use brackets around [TEXT].

This statement is a valid one only because the brackets:

CREATE TABLE IF NOT EXISTS [TABLE] (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, [TEXT] TEXT);

answered Jun 5 at 22:15

Luis Ramirez De La Rosa

111 bronze badge

Collectives™ on Stack Overflow

CREATE TABLE IF NOT EXISTS statement in SQLite

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related