Why does SQLite Database rowid created by itself? Android

Question

Is there a way to create a table without an AUTOINCREMENT column?

I have tried the solution from this link, but it didn't work for me. prevent autoincrementing integer primary key?

public static final String KEY_ID = "ROW_ID";                   
public static final String BOOK   = "books";    
public static final String CAR      = "cars";   
private static final String BOOK_CAR_TABLE = "BOOK_CAR_Table";
private static final String[] ALL_KEYS = new String[] {KEY_ID, BOOK, CAR};

private static final String BOOK_CAR_CREATE =           
 "CREATE TABLE " + BOOK_CAR_TABLE + "("+ 
                                KEY_ID + " INTEGER PRIMARY KEY," + 
                                BOOK + " TEXT," + 
                                CAR + " TEXT)";

I understand that when I add INTEGER PRIMARY KEY, it will turn KEY_ID into an AUTOINCREMENT column.

public static final String KEY_ID = "ROW_ID";                   
public static final String BOOK   = "books";    
public static final String CAR      = "cars";   
private static final String BOOK_CAR_TABLE = "BOOK_CAR_Table";
private static final String[] ALL_KEYS = new String[] {KEY_ID, BOOK, CAR};

private static final String BOOK_CAR_CREATE =           
 "CREATE TABLE " + BOOK_CAR_TABLE + "("+ 
                                KEY_ID + " INTEGER," + 
                                BOOK + " TEXT," + 
                                CAR + " TEXT)";

the AUTOINCREMENT rowid column was created by itself, how did it happen ?????

I don't want this AUTOINCREMENT column.

How can I prevent this?

Thank you

Edit: added code

private static final String BOOK_CAR_CREATE =           
 "CREATE TABLE " + BOOK_CAR_TABLE + "("+ 
                                KEY_ID + " INTEGER," + 
                                BOOK + " TEXT," + 
                                CAR + " TEXT) WITHOUT ROWID";

I've tried this and it was force close. I guess it's because of the the SQLite version?

this is a feature, not a bug – J-Dizzle Sep 29 '14 at 16:49 — J-Dizzle, Sep 29 '14 at 16:49

score 2 · Answer 1 · edited May 23 '17 at 11:43

2

You can specify WITHOUT ROWID but you will still need a PRIMARY KEY.

For example:

CREATE TABLE "table"("key" TEXT PRIMARY KEY, "value" TEXT) WITHOUT ROWID

This requires sqlite 3.8.2 or newer. The question is tagged also with android-sqlite so it's possible the sqlite version on your Android device is older.

Further reading: http://www.sqlite.org/withoutrowid.html

edited May 23 '17 at 11:43

Community

1
1

answered Sep 29 '14 at 16:46

laalto

150,114
66
286
303

In another words, it has to have the AUTOINCREMENT column? Thanks – YRTM2014 Sep 29 '14 at 17:05
It has to have a `PRIMARY KEY` but the it doesn't really have to be `INTEGER PRIMARY KEY` or `INTEGER PRIMARY KEY AUTOINCREMENT`. – laalto Sep 29 '14 at 17:23
I have updated my question. I've tried with "WITHOUT ROWID" but it was force close. It's probably because of the SQLite version. Thanks – YRTM2014 Sep 29 '14 at 18:04

Why does SQLite Database rowid created by itself? Android

1 Answers1