SQLite: prevent duplicates

Question

Hi,

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?

Thanks!

Answer 1

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.

Answer 2

You can use the column constraint UNIQUE.

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

Answer 3

Are you trying to prevent it through code, or in the database?

If trying to prevent from code you'll need to do a SELECT and see if the row already exists.

If trying to prevent it from the database you'll need to add a UNIQUE constraint on whichever row you don't want to allow duplicates on.

Answer 4

In your case, the correct primary key would have been parameter_name.