I've created a table which has a column "_id" which is an INTEGER type with AUTOINCREMENT. Now I know that a column which is on AUTOINCREMENT has to be a PRIMARY KEY , which is the syntax if I'm not wrong. But I want Composite keys so I don't declare the "_id" as (_id INTEGER PRIMARY KEY AUTOINCREMENT)with the PRIMARY KEY defintion in the column definition but declare it as (_id INTEGER AUTOINCREMENT, username TEXT UNIQUE, password TEXT, name TEXT, phonenumber TEXT, address TEXT, fine INTEGER, bookid INTEGER, PRIMARY KEY (_id, username, password))
But it gives a runtime error. Is it the case that when we have an AUTOINCREMENT field it's compulsory to declare the PRIMARY KEY in the column definition itself?
Here's the error
07-18 12:47:22.916: E/AndroidRuntime(3423): Caused by: android.database.sqlite.SQLiteException: near "AUTOINCREMENT": syntax error (code 1): , while compiling: CREATE TABLE userdetails (_id INTEGER AUTOINCREMENT, username TEXT UNIQUE, password TEXT, name TEXT, phonenumber TEXT, address TEXT, fine INTEGER, bookid INTEGER, PRIMARY KEY (_id, username, password));
No, the column does not need to be a primary key. However, SQLite allows AUTOINCREMENT only if one key is set as primary key. Here multiple columns are set as Primary Key. Hence auto increment on one of them does not work.
Refer to the answer to this stackoverflow post :