title: Find duplicate entries in SQLite database with Android
body: <p>I am using a SQLite database in Android and the user has to input name, ip and url which is then saved in the db. I want to be able to recognize if there are double entries in the name column but I do not know how to program it. I think making the column <code>UNIQUE</code> would be the correct way...</p>
  
  <p>My SQLAdapter:</p>
  
  <pre><code>public class ProjectsDBAdapter {
  
   public static final String KEY_ROWID = "_id";
   public static final String KEY_PROJECTNAME = "projectname";
   public static final String KEY_ROUTERIP = "routerip";
   public static final String KEY_URL = "url";
   public static final String KEY_CALIMERO = "calimero";
  
   private static final String TAG = "ProjectsDBAdapter";
   private DatabaseHelper mDbHelper;
   private SQLiteDatabase mDb;
  
   private static final String DATABASE_NAME = "KNXTable";
   private static final String SQLITE_TABLE = "Project";
   private static final int DATABASE_VERSION = 1;
  
   private final Context mCtx;
  
   private static final String DATABASE_CREATE =
    "CREATE TABLE if not exists " + SQLITE_TABLE + " (" +
    KEY_ROWID + " integer PRIMARY KEY autoincrement," +
    KEY_ROUTERIP + "," +
    KEY_PROJECTNAME + "," +
    KEY_URL + "," +
    KEY_CALIMERO + "," +
    "UNIQUE("+KEY_PROJECTNAME+")"+");";
  
   private static class DatabaseHelper extends SQLiteOpenHelper {
  
    DatabaseHelper(Context context) {
     super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
  
    @Override
    public void onCreate(SQLiteDatabase db) {
     //invoked when the database is created, 
     //this is where we can create tables and columns to them, create views or triggers.
     Log.w(TAG, DATABASE_CREATE);
     db.execSQL(DATABASE_CREATE);
    }
  
    public long createProject(String ip, String name, 
     String url, String calimero) {
  
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_ROUTERIP, ip);
    initialValues.put(KEY_PROJECTNAME, name);
    initialValues.put(KEY_URL, url);
    initialValues.put(KEY_CALIMERO, calimero);
  
    return mDb.insert(SQLITE_TABLE, null, initialValues);
    }
  </code></pre>
  
  <p>I hope you can give me some hints, I already tried to use UNIQUE when creating the table but without success...</p>
  
  <p><em><strong>UPDATE</em></strong></p>
  
  <p>The method in the SQLiteAdapter:</p>
  
  <pre><code> public long createProject(String ip, String name, 
     String url, String calimero) {
  
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_ROUTERIP, ip);
    initialValues.put(KEY_PROJECTNAME, name);
    initialValues.put(KEY_URL, url);
    initialValues.put(KEY_CALIMERO, calimero);
  
    return mDb.insertOrThrow(SQLITE_TABLE, null, initialValues);
   }
  </code></pre>
  
  <p>The call in my activity: </p>
  
  <pre><code> //DATABASE
   // Add project to Database
   dbHelper = new ProjectsDBAdapter(this);
   dbHelper.open();
  
   //Add Strings to database
   dbHelper.insertSomeProjects(IP, Name, URL, Calimero);
   //Generate ListView from SQLite Database
   displayListView();
  </code></pre>
  
  <p>and logcat output:</p>
  
  <pre><code>01-27 00:02:56.555: E/AndroidRuntime(28526): FATAL EXCEPTION: main
  01-27 00:02:56.555: E/AndroidRuntime(28526): java.lang.RuntimeException: Unable to start activity ComponentInfo{de.bertrandt.bertrandtknx/de.bertrandt.bertrandtknx.ProjectList}: android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1970)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1995)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.app.ActivityThread.access$600(ActivityThread.java:128)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1161)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.os.Handler.dispatchMessage(Handler.java:99)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.os.Looper.loop(Looper.java:137)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.app.ActivityThread.main(ActivityThread.java:4514)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at java.lang.reflect.Method.invokeNative(Native Method)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at java.lang.reflect.Method.invoke(Method.java:511)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:993)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:760)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at dalvik.system.NativeStart.main(Native Method)
  01-27 00:02:56.555: E/AndroidRuntime(28526): Caused by: android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.database.sqlite.SQLiteStatement.native_executeInsert(Native Method)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:113)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1839)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.database.sqlite.SQLiteDatabase.insertOrThrow(SQLiteDatabase.java:1738)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at SQLite.ProjectsDBAdapter.createProject(ProjectsDBAdapter.java:89)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at SQLite.ProjectsDBAdapter.insertSomeProjects(ProjectsDBAdapter.java:140)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at de.bertrandt.bertrandtknx.ProjectList.setup(ProjectList.java:120)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at de.bertrandt.bertrandtknx.ProjectList.onCreate(ProjectList.java:55)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.app.Activity.performCreate(Activity.java:4562)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1053)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1934)
  01-27 00:02:56.555: E/AndroidRuntime(28526):    ... 11 more
  </code></pre>
  
  <p><em><strong>SOLUTION</em></strong></p>
  
  <pre><code>//Add Strings to database
  try {
       dbHelper.insertSomeProjects(IP, Name, URL, Calimero);
  } catch (SQLiteException exception) {
       Log.d("SQLite", "Error"+exception.toString());
       Toast.makeText(getApplicationContext(),
          "Name is duplicated", Toast.LENGTH_SHORT).show();
              exception.printStackTrace();
  }
  </code></pre>