CODEWITHSUNDEEP
androidsqliteandroid-sqliteunique-constraint
TapanHP
TapanHP

Reputation: 6181

SQLite table constraint unique and ON CONFLICT REPLACE usage

Please read before make it Duplicate.

I am trying to insert longitude and latitude values in SQLite table and i want it to be unique value, after some research for that i have found this and this answers very helpful, although they both are written for simple queries and i am not able to find how to fit my query with both constraints unique and ON_CONFLICTION_REPLACE

query is as follow,

 private static final String CREATE_USER_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_LOCATIONS + "("
                + UID + " TEXT PRIMARY KEY," + ADDRESS + " TEXT,"
                + LONGITUDE + " TEXT unique," + LATITUDE + " TEXT unique" + ")";

but this unique doesn't made any differences, is it compulsion to use ON_CONFLICTION_REPLACE in my case?

From Ayman's answer i got that i can apply unique on multiple columns together but i still getting duplicate values after insertion. can anyone make appropriate query from my query and help me understand all things? Thanks

Upvotes: 1

Views: 5655

Answers (1)

Samuel Robert
Samuel Robert

Reputation: 11032

Change your table structure to this

private static final String CREATE_USER_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_LOCATIONS + "("
            + UID + " TEXT PRIMARY KEY," + ADDRESS + " TEXT,"
            + LONGITUDE + " TEXT," + LATITUDE + " TEXT,
            UNIQUE(" + LOGITUDE + "," + LATITUDE + ") ON CONFLICT REPLACE)";

Then when you do the insert use the below method

ContentValues insertValues = new ContentValues();
insertValues.put(LATITUDE, latitude);
insertValues.put(LOGITUDE, longitude);
db.insertWithOnConflict(TABLE_LOCATIONS, null, insertValues, SQLiteDatabase.CONFLICT_REPLACE);

Upvotes: 1

Related Questions