UNIQUE constraint failed: sqlite database : android

Question

I am trying to insert values in table. But there is only one value inserted. I am getting an error in log cat when I am trying to insert new values.

Log cat shows :

abort at 13 in [INSERT INTO event(totalminutesfrom,dayofweek,title,location,totalminutesto,id) VALUES (?,?,?,?,?,?)]: UNIQUE constraint failed: event.id
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: Error inserting totalminutesfrom=694 dayofweek=null title=qxs location=Eded & Mariz totalminutesto=0 id=0
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase: android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: event.id (code 1555)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:     at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:     at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:782)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:     at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:     at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1471)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1341)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:     at com.example.siddhi.timetablelayout.EventTableHelper.addEvent(EventTableHelper.java:76)
01-24 11:34:39.764 7763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:     at com.example.siddhi.timetablelayout.AddEventActivity$5.onClick(AddEventActivity.java:217)

Its showing error on these two lines while inserting row.

db.insert(TABLE, null, values);

   db.addEvent(new EventData(eventTitle,dayOfWeek,totalMinutesFrom, totalMinutesTo,location));

EventTableHelper

public class EventTableHelper extends SQLiteOpenHelper {


    private static final String TABLE = "event";
    private static final String KEY_ID = "id";
    private static final String KEY_TITLE = "title";
    private static final String KEY_LOCATION = "location";
    private static final String KEY_DAY_OF_WEEK = "dayofweek";
    private static final String KEY_TOTAL_MINUTES_FROM = "totalminutesfrom";
    private static final String KEY_TOTAL_MINUTES_TO = "totalminutesto";



    public EventTableHelper(Context context) {
        super(context, Constants.DATABASE_NAME, null, Constants.DATABASE_VERSION);
        //3rd argument to be passed is CursorFactory instance
    }

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
        //createTable(db);
    }

    public void createTable(SQLiteDatabase db){
        String CREATE_EVENTS_TABLE = "CREATE TABLE " + TABLE + "("
                + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TITLE + " TEXT,"
                + KEY_DAY_OF_WEEK +"TEXT" + KEY_TOTAL_MINUTES_FROM +"INTEGER"
                + KEY_TOTAL_MINUTES_TO + "INTEGER" +  KEY_LOCATION + "TEXT" +  ")";

        db.execSQL(CREATE_EVENTS_TABLE);

    }
    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
       // db.execSQL("DROP TABLE IF EXISTS " + TABLE);

      //  createTable(db);

        // Create tables again
        //onCreate(db);
    }

    // code to add the new contact
    public void addEvent(EventData event) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_ID, event.getId());
        values.put(KEY_TITLE,event.getTitle()); // Contact Name
        values.put(KEY_DAY_OF_WEEK,event.getDayofWeek());
        values.put(KEY_TOTAL_MINUTES_FROM,event.getFromMinutes());
        values.put(KEY_TOTAL_MINUTES_TO,event.getToMinutes());
        values.put(KEY_LOCATION,event.getLocation());
        // Inserting Row
        db.insert(TABLE, null, values);
        //2nd argument is String containing nullColumnHack
        db.close(); // Closing database connection
    }

    // code to get the single contact
   EventData getEvent(int id) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE, new String[] { KEY_ID,
                        KEY_TITLE, KEY_DAY_OF_WEEK, KEY_TOTAL_MINUTES_FROM,KEY_TOTAL_MINUTES_TO,KEY_LOCATION }, KEY_ID + "=?",
                new String[] { String.valueOf(id) }, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();
       EventData eventData = new EventData(Integer.parseInt(cursor.getString(0)),cursor.getString(1), cursor.getString(2),
               cursor.getInt(3),cursor.getInt(4),cursor.getString(5));

        return eventData;
    }



    // code to get all contacts in a list view
    public List<EventData> getAllEvents() {
        List<EventData> conList = new ArrayList<EventData>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE;

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {

                EventData event = new EventData();

                event.setId(Integer.parseInt(cursor.getString(0)));
                event.setTitle(cursor.getString(1));
                event.setDayofWeek(cursor.getString(2));
                event.setFromMinutes(cursor.getInt(3));
                event.setToMinutes(cursor.getInt(4));
                event.setLocation(cursor.getString(5));
                // Adding contact to list
                conList.add(event);
            } while (cursor.moveToNext());
        }

        // return contact list
        return conList;
    }
}

How to solve this??

You're trying to insert an already existing id. Since the key is UNIQUE, you can't. — Phantômaxx, Jan 24 '16 at 19:11
I'd make the key an AUTOINCREMENT one. And then I'd not pass the ID, since it would be automatically generated. — Phantômaxx, Jan 24 '16 at 19:24
https://stackoverflow.com/a/45299979/6665568 . This answer fully fixed the issue for me . Handles cases if u directly wanna replace if row already present. — Natesh bhat, Mar 18 '19 at 13:05

score 116 · Answer 1 · answered Jun 17 '18 at 11:45

116

For developers using Room Persistence Library. You can use

@Insert(onConflict = OnConflictStrategy.REPLACE)  // or OnConflictStrategy.IGNORE

in DAO according to Insert Documentation

answered Jun 17 '18 at 11:45

Dasser Basyouni

3,142
5
26
50

but it's just going to replace an existing row... if you really want to update the item then you should use `@Update`, `@Insert` is for adding new items – user155 Mar 11 '19 at 07:57
1

Thanks. This answer should be accepted instead of above one. – sagar.android Feb 21 '20 at 11:36
BE AWARE IF YOU USE CASCADE DELETION it will remove all related objects to this related object – user155 Feb 17 '21 at 23:03
1

I am still getting the exception even with `OnConflictStrategy.REPLACE/IGNORE`. My composite primary key is not unique, but I would like room to just ignore these cases instead of crashing with `SQLiteConstraintException`. How can I fix? – mars8 Jul 10 '22 at 18:19

score 42 · Accepted Answer · answered Jan 24 '16 at 19:14

42

Your code probably violates primary key's uniqueness constraint on a KEY_ID field.

Two possible solutions are:

Make sure that your EventData.getId() returns unique values per object. For now, I don't see you pass any identifier to its constructor and perhaps all the events are inserted with the same id value.
If you don't care about generating ids by yourself, you can add AUTOINCREMENT setting to your KEY_ID column definition. This way KEY_ID field will be filled automatically and each row will have its own, unique value. Once there, don't forget to remove adding KEY_ID to ContentValues by yourself.

answered Jan 24 '16 at 19:14

Mateusz Herych

1,595
12
20

thank u so much... second solution worked for me...Did not insert the id..@Mateusz Herych – Jan 24 '16 at 19:45
Wow, first one is working for me thanks, @Mateusz Herych – Surendar D Apr 17 '17 at 11:07
A question is, could we surround the insert with a try catch ? – thahgr Feb 10 '20 at 13:12
i guess no, because insert is done by a thread asynchronously – thahgr Feb 10 '20 at 13:20

user155 · Answer 3 · 2019-03-11T07:59:41.740

15

If you use Room then instead of @PrimaryKey you should use @PrimaryKey(autoGenerate = true)

and make your id variable optional:

@Entity(tableName = "contact_table") data class Contact(@PrimaryKey(autoGenerate = true) @ColumnInfo(name = "id") var id: Long?, @ColumnInfo(name = "name") val name: String, @ColumnInfo(name = "phone") val phone: String)

and then when adding a new item, pass null as id, insert func will return new id, add it to your object

val contact = Contact(null, name, phone)
contact.id = ContactRoomDatabase.getDatabase().contactDao().insert(contact)

edited Mar 11 '19 at 07:59

answered Mar 11 '19 at 07:38

user155

775
1
7
25

make the `id` nonnull is also ok. Pass `0` to id, `insert` func will also return new id – Yeung Jun 22 '21 at 08:27

score 9 · Answer 4 · answered Oct 21 '20 at 10:13

This is the case of duplicate id of the record in the database. Just clear the app storage and try again. A good solution would be adding below in your Dao class.

    @Insert (onConflict = OnConflictStrategy.REPLACE)

Correct solution to this problem is to make sure that the id is unique. Please have a look at this Google Codelabs Room Example

Another way to avoid this would be adding @PrimaryKey(autoGenerate = true)

score 7 · Answer 5 · answered Jan 24 '16 at 19:13

The table has a unique constraint on it. That means that only one row can exist with a given ID value. If you're trying to change some of the values for a row, use UPDATE not INSERT. If you're trying to add this row, you need to either give it a different, unique ID or you need to delete the pre-existing row first. Which of these is the right answer depends on what your app is doing.

score 6 · Answer 6 · answered Aug 02 '16 at 18:25

6

Try checking if the ID is already existed. If true, do not insert, because you already have the row with this ID.

answered Aug 02 '16 at 18:25

Kai Wang

3,303
1
31
27

Samir · Answer 7 · 2021-01-09T18:17:22.480

4

My mistake was, I tried to fill ID column though it was already defined as INTEGER PRIMARY KEY AUTOINCREMENT

edited Jan 09 '21 at 18:17

answered Jul 06 '17 at 08:38

Samir

6,405
5
39
42

score 3 · Answer 8 · answered Aug 21 '21 at 21:37

For Kotlin model classes

If you're assigning a default value to the primary key like this:

data class MyModel(
 @PrimaryKey(autoGenerate = true) val id: Int = defaultValue
)

Make sure default value is 0 for Long and Int types.

From the autoGenerate comment:

If the field type is long or int (or its TypeConverter converts it to a long or int), Insert methods treat 0 as not-set while inserting the item.

In my case, I used -1 as the defaultValue and this error was occurring.

score 2 · Answer 9 · answered Nov 07 '17 at 09:52

2

make id column id integer autoincrement, and do not put the id value into content values.

answered Nov 07 '17 at 09:52

Vickie Kangare

17
2

score 1 · Answer 10 · answered Jul 04 '17 at 10:10

1

I originally put the new unique constraint infant of the old one. Instead make sure you're current unique column is first:

CREATE TABLE TEST (client_id TEXT unique, remote_id INT unique)

answered Jul 04 '17 at 10:10

HannahCarney

3,441
2
26
32

score 0 · Answer 11 · answered Nov 02 '18 at 08:52

0

I had the same problem and the problem was that I forgot to add db.execSQL(YOUR_TABLE); in my DbHelper

answered Nov 02 '18 at 08:52

Neuron

1,020
2
13
28

score 0 · Answer 12 · answered Jan 04 '22 at 10:55

0

Guys i had the same problem and i cleared my local database from android settings and a clear run the app, that is work for me. Maybe you made a little change in db and you forgot.

answered Jan 04 '22 at 10:55

Umut Can

41
2

score 0 · Answer 13 · answered Mar 08 '23 at 12:53

0

I solved it by didn't insert the primary key manually when it is autoGenerate, this means that instead of putting value in the insert method for id, try to create an instance of entity class and use it in the insert method

answered Mar 08 '23 at 12:53

faribakhandani

39
5

UNIQUE constraint failed: sqlite database : android

13 Answers13

Linked

Related