CODEWITHSUNDEEP
androidsqliteunique-constraint
lis
lis

Reputation: 680

android sqlite unique insert

Just a simple SQLite question...

I've created a table like this:CREATE TABLE mytable (id INTEGER PRIMARY KEY AUTOINCREMENT, column1 TEXT, column2 TEXT, UNIQUE (column1, column2)) (I also tried it with the extension ON CONFLICT REPLACE)

But if I call my insert method on the second time with the same values, they will be added. E.g.

insert("one", "two"); 
insert("one", "two");

fills the table like this:

1 | one | two
2 | one | two

but it should look like this after the second call:

1 | one | two

That's my insert function:

public void insert(String a, String b){
    ContentValues contentValues = new ContentValues();
    contentValues.put("column1", a);
    contentValues.put("column2", b);
    try {
        //database.insertWithOnConflict("mytable", null, contentValues, SQLiteDatabase.CONFLICT_IGNORE);
        //database.insertWithOnConflict("mytable", null, contentValues, SQLiteDatabase.CONFLICT_REPLACE);
        database.insertOrThrow("mytable", null, contentValues);
    } catch (SQLiteConstraintException e) {
        Log.e("insert", "SQLException: " + e.getLocalizedMessage());
    }
}

EDIT:

if I call 

insert("one", "two"); 
insert("three", "two");

the table should be filled like this:

1 | one | two
2 | three | two

Upvotes: 2

Views: 2916

Answers (1)

Moubeen Farooq Khan
Moubeen Farooq Khan

Reputation: 2885

what you can do is to write UNIQUE with each column in the table

CREATE TABLE mytable (id INTEGER PRIMARY KEY AUTOINCREMENT, column1 TEXT UNIQUE IGNORE, column2 TEXT UNIQUE IGNORE)

ignore is to, ignore the value that is not unique

When an applicable constraint violation occurs, the IGNORE resolution algorithm skips the one row that contains the constraint violation and continues processing subsequent rows of the SQL statement as if nothing went wrong. Other rows before and after the row that contained the constraint violation are inserted or updated normally. No error is returned when the IGNORE conflict resolution algorithm is used.

As You have updated your question what you have to do is to remove UNIQUE AND IGNORE FROM COLUMN in which you need duplicated values.

hope that helps.

Upvotes: 1

Related Questions