SQLite: prevent duplicates

Question

I would like to create a table to store device settings. The table has three rows: id, parameter_name and parameter_value.

The table was created by executing the following query statement:

DATABASE_CREATE = "create table DATABASE_TABLE (KEY_ID INTEGER PRIMARY KEY AUTOINCREMENT, KEY_NAME INTEGER not null, VALUE TEXT not null);

and then the rows are stored by executing the following method:

private long insertRow(int rowParameter, String rowValue, SQLiteDatabase db){
    long res = -1;
    ContentValues settingsParameterValues = new ContentValues();
    settingsParameterValues.put(KEY_NAME, rowParameter);
    settingsParameterValues.put(VALUE, rowValue);
    if(db != null){
        res = db.insert(DATABASE_TABLE, null, settingsParameterValues);
    }
    return res;
}

When the database is created the default values are stored:

@Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DATABASE_CREATE);
        insertRow(PRIVACY_LEVEL_ROW_INDEX, "0", db);
        insertRow(STREAM_TITLE_ROW_INDEX, "untitled", db);
        insertRow(STREAM_TAGS_ROW_INDEX, "", db);
    }

The problem with method insertRow() however is that it can't prevent duplicating the entries.

Does anyone know how to prevent duplicate entries in this case?

Answer 1

You can use the column constraint UNIQUE.

The UNIQUE constraint causes an unique index to be created on the specified columns.

Answer 2

I would create a UNIQUE CONSTRAINT in the database table definition. That will automatically prevent duplicate entries and it's pretty much the only concurrency-safe way to do it!

Also, don't forget about CHECK CONSTRAINT which may be more helpful if you need to define more complex rules for the data in you tables.