Android SQLite - Primary Key - Inserting into table
Solution 1
To make the database auto-generate the primary key for you, just don't specify it yourself. Remove this line from the insert code:
values.put(KEY_P, journey.getpKey());
You can capture the generated id from the return value of insert()
.
Also, I want the data to be in descending order, to do this, I use DESC, but where shall I place this?
Assuming this applies to getAllJourneys()
where you do a rawQuery()
, just add the ORDER BY
directly in the SQL:
String selectQuery = "SELECT * FROM " + TABLE_JOURNEY + " ORDER BY " + KEY_P + " DESC";
Solution 2
Go through the following steps
Modify your Creating Tables code like this
// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_JOURNEY_TABLE = "CREATE TABLE " + TABLE_JOURNEY + "("
+ KEY_P + " INTEGER PRIMARY KEY AUTOINCREMENT DEFAULT 1 ," + KEY_ID + " TEXT," + KEY_DIST + " TEXT,"
+ KEY_MPG + " TEXT," + KEY_COST + " TEXT )";
db.execSQL(CREATE_JOURNEY_TABLE);
}
by using
INTEGER PRIMARY KEY AUTOINCREMENT DEFAULT 1
you can start increment from 1
Then remove the following code
values.put(KEY_P, journey.getpKey());
Frog82
Updated on July 23, 2020Comments
-
Frog82 almost 4 years
Im in my final stages of creating a database for my android app, however, I cannot seem to get my primary key to increment. Here is my code where I set it up,
public class DatabaseHandler extends SQLiteOpenHelper { // All Static variables // Database Version private static final int DATABASE_VERSION = 17; // Database Name private static final String DATABASE_NAME = "journeyManager"; // Contacts table name public static final String TABLE_JOURNEY = "journey"; // Contacts Table Columns names private static final String KEY_P = "key"; private static final String KEY_ID = "id"; private static final String KEY_DIST = "distance"; private static final String KEY_MPG = "mpg"; private static final String KEY_COST = "cost"; public DatabaseHandler(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } // Creating Tables @Override public void onCreate(SQLiteDatabase db) { String CREATE_JOURNEY_TABLE = "CREATE TABLE " + TABLE_JOURNEY + "(" + KEY_P + " INTEGER PRIMARY KEY," + KEY_ID + " TEXT," + KEY_DIST + " TEXT," + KEY_MPG + " TEXT," + KEY_COST + " TEXT )"; db.execSQL(CREATE_JOURNEY_TABLE); } // Upgrading database @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // Drop older table if existed db.execSQL("DROP TABLE IF EXISTS " + TABLE_JOURNEY); // Create tables again onCreate(db); } /** * All CRUD(Create, Read, Update, Delete) Operations */ // Adding new contact void addJourneyData(Journey journey) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(KEY_P, journey.getpKey()); values.put(KEY_ID, journey.getId()); values.put(KEY_DIST, journey.getDistance()); // Contact Name values.put(KEY_MPG, journey.getMpg()); // Contact Phone values.put(KEY_COST, journey.getCost()); // Contact Phone // Inserting Row db.insert(TABLE_JOURNEY, null, values); db.close(); // Closing database connection } // Getting single contact Journey getJourney(int id) { SQLiteDatabase db = this.getReadableDatabase(); Cursor cursor = db.query(TABLE_JOURNEY, new String[] { KEY_P + KEY_ID + KEY_DIST, KEY_MPG, KEY_COST }, KEY_P + "=?", new String[] { String.valueOf(id) }, null, null, null, null); if (cursor != null) cursor.moveToFirst(); Journey journey = new Journey(); journey.setPkey(Integer.parseInt(cursor.getString(0))); journey.setId(String.valueOf(cursor.getString(1))); journey.setMpg(String.valueOf(cursor.getString(2))); journey.setDistance(String.valueOf(cursor.getString(3))); journey.setCost(String.valueOf(cursor.getString(4))); // return contact return journey; } // Getting All Contacts public List<Journey> getAllJourneys() { List<Journey> journeyList = new ArrayList<Journey>(); // Select All Query String selectQuery = "SELECT * FROM " + TABLE_JOURNEY; SQLiteDatabase db = this.getWritableDatabase(); Cursor cursor = db.rawQuery(selectQuery, null); // looping through all rows and adding to list if (cursor.moveToFirst()) { do { Journey journey = new Journey(); journey.setPkey(Integer.parseInt(cursor.getString(0))); journey.setId(String.valueOf(cursor.getString(1))); journey.setMpg(String.valueOf(cursor.getString(2))); journey.setDistance(String.valueOf(cursor.getString(3))); journey.setCost(String.valueOf(cursor.getString(4))); // Adding contact to list journeyList.add(journey); } while (cursor.moveToNext()); } // return contact list return journeyList; } }
Here is where I add the details into a database from a button on another activity,
db.addJourneyData(new Journey(1,timeStamp, distanceLabel, mpgAnswer, pplAnswer));
I get to the point, where it will add the first one in, but from then on it will say the primary key is not unique - therefore it doesn't update the database.
Also, I want the data to be in descending order, to do this, I use DESC, but where shall I place this?
Any help would be appreciated,
Many thanks,