How to prevent duplicate data in android sqlite database?

Question

Iam working on sqlite database in android i want to insert a table like below

                    id procode  proname user
                    1  pro123   mobile   120
                    2  pro123   mobile   121
                    3  pro345   watches  120

in the above table only id is uniq all values are not uniq but i dont want to allow same data for same user it means another user will insert same data now i dont want to insert duplicate while checking data of procode and user means like bwlow

                    id procode  proname user
                    1  pro123   mobile   120
                    2  pro123   mobile   121
                    3  pro123    mobile   120

in the above table 3 is duplicate data

you can check https://stackoverflow.com/questions/734689/sqlite-primary-key-on-multiple-columns for more than one primary keys in your data base — Shayan D, Jul 21 '18 at 06:36
Before you insert a new row, do a query to check if that data already exists — OneCricketeer, Jul 21 '18 at 06:49

score 5 · Accepted Answer · answered Jul 21 '18 at 06:58

When you insert new row you have the new USER.

So before inserting data into your DB.

Check whether already same USER data exists or not.

Below is SQLITE Query hint for the same

Cursor c=db.rawQuery("SELECT * FROM user WHERE user="YOUR_VALUE", null);
if(c.moveToFirst())
{
 showMessage("Error", "Record exist");
}
else
{
 // Inserting record
}

score 0 · Answer 2 · answered Jul 21 '18 at 06:35

0

You need to create a unique key when you create your table

CREATE TABLE name (<your column definitions>, UNIQUE (procode, proname, user) ON CONFLICT REPLACE);

To handle what happens on a conflict, see https://sqlite.org/lang_conflict.html

answered Jul 21 '18 at 06:35

Arun

3,036
3
35
57

score 0 · Answer 3 · answered Jan 14 '20 at 18:13

I have found the best solution in the process of trial and error In the Main Activity

 int count = 0;
        while(db.findTask(results.toUpperCase()).moveToNext()){
            count++;
            break;
        }
        if(count!=0){
            Snackbar.make(v, "Name Already Taken", Snackbar.LENGTH_LONG)
                    .setAction("Action", null).show();
            return;
  }

And in the DataBase

public Cursor findTask(String taskName){
    SQLiteDatabase db = this.getWritableDatabase();
    String query = "SELECT "+ID+ " FROM "+TABLE_NAME+" WHERE "+
            TASK_NAME + " = '"+taskName+"'";
    Cursor data = db.rawQuery(query,null);
    return data;

score 0 · Answer 4 · answered Aug 10 '23 at 09:42

0

Cursor c=databasemanager.rawQuery("SELECT * FROM " + **TABLE_NAME** + " WHERE " + **COLUMN_NAME_IN_WHICH_YOU_ARE_SEARCHING** +" = " + "'"+**what are you searching**+"'", null);
        if(c.moveToFirst())
        {
//            showMessage("Error", "Record exist");
        }
        else
        {
            // Inserting record
            ContentValues cv = new ContentValues();
            cv.put(COLUMN_NAME_IN_WHICH_YOU_ARE_SEARCHING , what are you searching);
            db.insert(TABLE_NAME ,null, cv);
        }

answered Aug 10 '23 at 09:42

Zum_testing Zum

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 14 '23 at 13:48

How to prevent duplicate data in android sqlite database?

4 Answers4