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 · Accepted Answer · 2013-12-12 08:15:35Z

4

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.

edited Dec 12, 2013 at 8:15

answered Dec 12, 2013 at 7:58

laalto

153k68 gold badges293 silver badges306 bronze badges

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

Comments

Vaibhav Agarwal · Accepted Answer · 2013-12-12 08:43:01Z

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, 2013 at 8:43

answered Dec 12, 2013 at 7:42

Vaibhav Agarwal

4,4973 gold badges21 silver badges20 bronze badges

4 Comments

CL. Over a year ago

Removing AUTOINCREMENT would change the semantics, and a semicolon at the end of an SQL statement, while being superfluous, works just fine.

Vaibhav Agarwal Over a year ago

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.

CL. Over a year ago

Without AUTOINCREMENT, the IDs of deleted records might be reused. (Read the documentation!) There is nothing incorrect about the semicolon (and in any case this has nothing to do with the problem in the question).

Vaibhav Agarwal Over a year ago

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.

Mahad Ahmed · Accepted Answer · 2022-05-24 08:05:13Z

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, 2022 at 8:05

Mahad Ahmed

3095 silver badges8 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:01:31Z

-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, 2017 at 12:01

CommunityBot

11 silver badge

answered Dec 12, 2013 at 1:50

Prahlad Yeri

3,7015 gold badges30 silver badges70 bronze badges

1 Comment

laalto Over a year ago

The linked answer is wrong. SQLite supports AUTOINCREMENT and the semantics of INTEGER PRIMARY KEY and INTEGER PRIMARY KEY AUTOINCREMENT are slightly different. See sqlite.org/autoinc.html

Collectives™ on Stack Overflow

SQLite syntax error near "CREATE TABLE"

4 Answers 4

Comments

4 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related