SQLite: prevent duplicates

android, database, sql, sqlite

Solution

You can use the column constraint `UNIQUE`.

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

Problem

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?

Original source