SQLite syntax error near "CREATE TABLE"

Question

Here's my code:

    String CREATE_DATABASE = "CREATE TABLE " + TABLE_NAME + "("  + 
            KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
            "title TEXT, "+
            "author TEXT, "+
            "state TEXT);";

    db.execSQL(CREATE_DATABASE);

The LogCat says : 12-11 23:43:50.553: E/AndroidRuntime(3706): android.database.sqlite.SQLiteException: near "CREATE TABLE": syntax error (code 1): , while compiling: CREATE TABLE PapersTable(_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, author TEXT, state TEXT);

laalto · Answer 1 · 2013-12-12T08:15:35.143

The CREATE TABLE syntax is all right as you've posted it.

I suspect there's a non-breaking space (ASCII 0xA0) between CREATE and TABLE. Replace it with the regular space (ASCII 0x20). That would explain the syntax error you posted: parser is treating CREATE TABLE as a single unknown token and not as two separate known tokens CREATE and TABLE.

What is definitely wrong is that you call db.close() on the SQLiteDatabase db passed in as a parameter to your function. You should only close databases you opened yourself and closing it this way will lead to an exception, albeit a different one.

Vaibhav Agarwal · Answer 2 · 2013-12-12T08:43:01.343

0

Try this code in your onCreate method.

Remove AUTOINCREMENT for KEY_ID as it is already declared as Primary KEY.
Remove ";" you placed just after the last bracket in string i.e. second last semicolon in string CREATE_DATABASE.
Remove db.close() from oncreate()

Code To Replace: String CREATE_DATABASE = "CREATE TABLE "+TABLE_NAME+ " ( "+KEY_ID+" INTEGER PRIMARY KEY, "+KEY_TITLE+"TEXT, "+KEY_AUTHOR+"TEXT, "+KEY_STATE+"TEXT )"; db.execSQL(CREATE_DATABASE);

edited Dec 12 '13 at 08:43

answered Dec 12 '13 at 07:42

Vaibhav Agarwal

4,499
3
19
20

1

Removing AUTOINCREMENT would change the semantics, and a semicolon at the end of an SQL statement, while being superfluous, works just fine. – CL. Dec 12 '13 at 08:14
There is no need of AUTOINCREMENT in primary key attribute as this property is already acquired by the PRIMARY KEY. Semicolon at the end of create table bracket is not a correct way to create a table so plz remove that. db.close() is also not required in onCreate() as it is required when we perform some operations on database tables so you can remove that too. – Vaibhav Agarwal Dec 12 '13 at 08:45
1

Without AUTOINCREMENT, the IDs of deleted records might be reused. (Read the [documentation](http://www.sqlite.org/autoinc.html)!) There is nothing incorrect about the semicolon (and in any case this has nothing to do with the problem in the question). – CL. Dec 12 '13 at 08:53
yeah I agree but there is no problem if a record uses deleted id again.It will be a new entry with old id so user can access data easily. and yeah there is nothing incorrect about semicolon but it is useless , there is no need of that semicolon so one can omit that. – Vaibhav Agarwal Dec 12 '13 at 08:57

score 0 · Answer 3 · answered May 24 '22 at 08:05

0

Mine was missing the closing ) after the table body.

CREATE TABLE IF NOT EXIST user_table (
  id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  title TEXT,
  author TEXT,
  state TEXT
); -- this closing ')' was missing

answered May 24 '22 at 08:05

Mahad Ahmed

179
4
7

score -1 · Answer 4 · edited May 23 '17 at 12:01

-1

In SQLite, a column declared integer primary key will auto increment, so try removing that:

String CREATE_DATABASE = "CREATE TABLE " + TABLE_NAME + "("  + 
        KEY_ID + " INTEGER PRIMARY KEY, " + 
        "title TEXT, "+
        "author TEXT, "+
        "state TEXT);";

See this answer for reference.

edited May 23 '17 at 12:01

Community

1
1

answered Dec 12 '13 at 01:50

Prahlad Yeri

3,567
4
25
55

1

The linked answer is wrong. SQLite supports `AUTOINCREMENT` and the semantics of `INTEGER PRIMARY KEY` and `INTEGER PRIMARY KEY AUTOINCREMENT` are slightly different. See http://www.sqlite.org/autoinc.html – laalto Dec 12 '13 at 08:08

SQLite syntax error near "CREATE TABLE"

4 Answers4

Linked