UNIQUE constraint failed: sqlite database : android
JavaAndroidDatabaseSqliteUnique ConstraintJava Problem Overview
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??
Java Solutions
Solution 1 - Java
For developers using Room Persistence Library. You can use
@Insert(onConflict = OnConflictStrategy.REPLACE) // or OnConflictStrategy.IGNORE
in DAO according to Insert Documentation
Solution 2 - Java
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 3 - Java
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 - Java
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 - Java
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.
Solution 6 - Java
Try checking if the ID is already existed. If true, do not insert, because you already have the row with this ID.
Solution 7 - Java
My mistake was, I tried to fill ID
column though it was already defined as INTEGER PRIMARY KEY AUTOINCREMENT
Solution 8 - Java
make id column id integer autoincrement, and do not put the id value into content values.
Solution 9 - Java
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)
Solution 10 - Java
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.
Solution 11 - Java
I had the same problem and the problem was that I forgot to add db.execSQL(YOUR_TABLE);
in my DbHelper
Solution 12 - Java
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.