[FIXED] I can't save records with no duplicates in SQLite

Issue

I'm trying to save records with no duplicates in SQLite but even UNIQUE() doesn't help.

Here's where i create db and i want cityName to be unique:

@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
    sqLiteDatabase.execSQL("create table " + tableName + "(" + KEY_ID + "integer primary key autoincrement,"
    + cityName + " text," + cityTemp + " text, " + " UNIQUE(" + cityName +"))");
}

And function that stores in DB:

public void saveWeather(Response<CurrentWeather> response) {
    contentValues = new ContentValues();
    database = dbHelper.getWritableDatabase();
    contentValues.put(DBHelper.cityName, response.body().getCityName());
    contentValues.put(DBHelper.cityTemp, 
    response.body().getForecastMain().getTemp());
    database.insert(DBHelper.tableName, null, contentValues);
}

I expect no duplicates but they are

Solution

You issue was due to how the onCreate method runs.

It only runs automatically once for the lifetime of the database. As such any schema changes made to the create SQL will not automatically be applied.

When developing the typical work around is to delete the database or to utilise the onUpgrade method, if it drops the table(s) and recreates them (generally by calling onCreate) . The database can be deleted by deleting/clearing the the Apps data, uninstalling the App, or via Device Explorer.

If you need to keep existing data then it's a little more complicated.

Answered By - MikeT