Android table creation Failure (near "autoincrement": syntax error)?

Question

 public static final String MYDATABASE_NAME = "MY_DATABASE";
 public static final String MYDATABASE_TABLE = "MY_TABLE";
 public static final String MYDATABASE_TABLE2 = "MY_TABLE2";
 public static final int MYDATABASE_VERSION = 1;
 public static final String KEY_ID = "_id";
 public static final String KEY_ID2 = "_id2";
 public static final String KEY_CONTENT1 = "Content1";
 public static final String KEY_CONTENT2 = "Content2";
 public static final String KEY_CONTENT3 = "Content3";

 //create table MY_DATABASE (ID integer primary key, Content text not null);
 private static final String SCRIPT_CREATE_DATABASE = "create table " + MYDATABASE_TABLE + " (" 
                                                        +KEY_ID + " integer primary key autoincrement, " 
                                                        + KEY_CONTENT1 + " text not null);";

 private static final String SCRIPT_CREATE_DATABASE2 = "create table " + MYDATABASE_TABLE2 + " (" 
                                                        + KEY_ID2 + " integer autoincrement, " 
                                                        + KEY_CONTENT2 + " text not null, " 
                                                        + KEY_CONTENT3 + " text not null, "
                                                        + " FOREIGN KEY ("+KEY_ID2+") REFERENCES "+MYDATABASE_TABLE+" ("+KEY_ID+"));";

I can not find out what gives the following error, please help me out thank you.

09-29 13:41:19.760: ERROR/Database(334): Failure 1 (near "autoincrement": syntax error) on 0x218df0 when preparing 'create table MY_TABLE2 (_id2 integer autoincrement, Content2 text not null, Content3 text not null, FOREIGN KEY (_id2) REFERENCES MY_TABLE (_id));'.

09-29 13:41:19.770: DEBUG/AndroidRuntime(334): Shutting down VM

09-29 13:41:19.770: WARN/dalvikvm(334): threadid=1: thread exiting with uncaught exception (group=0x4001d800)

09-29 13:41:19.791: ERROR/AndroidRuntime(334): FATAL EXCEPTION: main

09-29 13:41:19.791: ERROR/AndroidRuntime(334): java.lang.RuntimeException: Unable to start activity ComponentInfo{sep.com/sep.com.SepActivity}: android.database.sqlite.SQLiteException: near "autoincrement": syntax error: create table MY_TABLE2 (_id2 integer autoincrement, Content2 text not null, Content3 text not null, FOREIGN KEY (_id2) REFERENCES MY_TABLE (_id));

Volauvent

Answer 1

In short: In SQLite a column declared INTEGER PRIMARY KEY will autoincrement. There is no autoincrement keyword in SQLite, that is why you are getting an error.

You can find out more on SQLite FAQ.

EDIT: just writing integer primary key it is enough. SQLite will automatically increment your ids.

EDIT2: Your onUpgrade() method should look like this :

  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (newVersion > oldVersion) {
            Log.w("MyAppTag","Updating database from version " + oldVersion + " to "
                    + newVersion + " .Existing data will be lost.");
            db.execSQL("DROP TABLE IF EXISTS " + MY_TABLE);
            db.execSQL("DROP TABLE IF EXISTS " + MY_TABLE2);
            onCreate(db);
        }

Undefined

thank you, but how can i add autoincrement to the code. im really new to android programming please be kind enf to tell me what i should do to the code. – Volauvent

i did what you have said and then i got this following error 09-29 14:16:58.580: ERROR/AndroidRuntime(464): java.lang.RuntimeException: Unable to start activity ComponentInfo{sep.com/sep.com.SepActivity}: android.database.sqlite.SQLiteException: no such column: Content2: , while compiling: SELECT _id, Content1, Content2, Content3 FROM MY_TABLE – Volauvent

I think you need to upgrade your database. increase the version and be sure that you properly implement onUpdate() – Undefined

Hi again, well how to do so... always when i do changers it wont get updated.. public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // TODO Auto-generated method stub } this is only in my upgrade() func. – Volauvent

Answer 2

This:

+ KEY_ID2 + " integer autoincrement, " 

should be this:

+ KEY_ID2 + " integer primary key autoincrement, " 

If you follow the syntax diagrams for CREATE TABLE you'll see that autoincrement should only come after primary key.

If you want _id2 to be a foreign key then you don't want it to be auto increment at all though.

Level

i did what you have said and then i got this following error 09-29 14:16:58.580: ERROR/AndroidRuntime(464): java.lang.RuntimeException: Unable to start activity ComponentInfo{sep.com/sep.com.SepActivity}: android.database.sqlite.SQLiteException: no such column: Content2: , while compiling: SELECT _id, Content1, Content2, Content3 FROM MY_TABLE – Volauvent

@LoshWick: The Content2 column is in MY_TABLE2 not MY_TABLE. – Level

private static final String SCRIPT_CREATE_DATABASE = "create table " + MYDATABASE_TABLE + " (" +KEY_ID + " integer primary key autoincrement, " + KEY_CONTENT1 + " text not null);"; private static final String SCRIPT_CREATE_DATABASE2 = "create table " + MYDATABASE_TABLE2 + " (" + KEY_ID2 + " integer primary key autoincrement, " + KEY_CONTENT2 + " text not null, " + KEY_CONTENT3 + " text not null, " + " FOREIGN KEY ("+KEY_ID2+") REFERENCES "+MYDATABASE_TABLE+" ("+KEY_ID+"));"; what have i done wrong here? – Volauvent

Answer 3

An above answer says that 'There is no autoincrement keyword in SQLite.' This is incorrect. There is a keyword called 'autoincrement' and I used it in the primary key.

For example:

   static final String CREATE_LOCATION_TABLE="CREATE TABLE Location"+
            "( LocationID INTEGER PRIMARY KEY AUTOINCREMENT," +
            " RestuarantID int not null," +
            " Latitude float not null," +
            " Longitude float not null)";

Also make sure when you enter data to this table use the keyword 'null' in the place of the primary key.

Like this:

   static final String INSERT_LOCATION= "Insert into Location values" +
            "(null,1002,6.905369,79.851514)";

Upwind

Answer 4

Three things:

• Remove the trailing ; from the statements.
• Add "primary key" constraint to the autoincrement column
• Remove autoincrement from PK columns - it will happen automatically.

Dougherty

Answer 5

sqlite> CREATE TABLE app (a INTEGER PRIMARY KEY NOT NULL, B VARCHAR);
sqlite> insert into app (b) values ('');
sqlite> insert into app (b) values ('a');
sqlite> 
sqlite> insert into app (b) values ('b');
sqlite> insert into app (b) values ('c');
sqlite> insert into app (b) values ('d');
sqlite> select * from app;
1|
2|a
3|b
4|c
5|d
sqlite> exit;

NOTE: IN SQLite there in no AUTOINCREMENT keyword. So, you have to just use INTEGER PRIMARY KEY NOT NULL. It will automatically insert the incremented value for the attribute.

Urethrectomy