UNIQUE constraint failed: sqlite database : android
Solution 1
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 sameid
value. - If you don't care about generating ids by yourself, you can add
AUTOINCREMENT
setting to yourKEY_ID
column definition. This wayKEY_ID
field will be filled automatically and each row will have its own, unique value. Once there, don't forget to remove addingKEY_ID
toContentValues
by yourself.
Solution 2
For developers using Room Persistence Library. You can use
@Insert(onConflict = OnConflictStrategy.REPLACE) // or OnConflictStrategy.IGNORE
in DAO according to Insert Documentation
Solution 3
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)
Solution 4
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)
Solution 5
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.
Admin
Updated on July 09, 2022Comments
-
Admin almost 2 years
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??
-
Admin over 8 yearsthank u so much... second solution worked for me...Did not insert the id..@Mateusz Herych
-
Surendar D about 7 yearsWow, first one is working for me thanks, @Mateusz Herych
-
user155 about 5 yearsbut 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 -
thahgr over 4 yearsA question is, could we surround the insert with a try catch ?
-
thahgr over 4 yearsi guess no, because insert is done by a thread asynchronously
-
sagar.android about 4 yearsThanks. This answer should be accepted instead of above one.
-
user155 about 3 yearsBE AWARE IF YOU USE CASCADE DELETION it will remove all related objects to this related object
-
Yeung almost 3 yearsmake the
id
nonnull is also ok. Pass0
to id,insert
func will also return new id