android 数据库 unique,UNIQUE constraint failed: sqlite database : android

可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):

由

问题:

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 at13in[INSERT INTOevent(totalminutesfrom,dayofweek,title,location,totalminutesto,id)VALUES(?,?,?,?,?,?)]:UNIQUE constraint failed:event.id01-2411:34:39.7647763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:Errorinserting totalminutesfrom=694dayofweek=nulltitle=qxs location=Eded&Mariztotalminutesto=0id=001-2411:34:39.7647763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:android.database.sqlite.SQLiteConstraintException:UNIQUE constraint failed:event.id(code1555)01-2411:34:39.7647763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(NativeMethod)01-2411:34:39.7647763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:782)01-2411:34:39.7647763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)01-2411:34:39.7647763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)01-2411:34:39.7647763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1471)01-2411:34:39.7647763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1341)01-2411:34:39.7647763-7763/com.example.siddhi.timetablelayout E/SQLiteDatabase:at com.example.siddhi.timetablelayout.EventTableHelper.addEvent(EventTableHelper.java:76)01-2411:34:39.7647763-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(newEventData(eventTitle,dayOfWeek,totalMinutesFrom,totalMinutesTo,location));

EventTableHelper publicclassEventTableHelperextendsSQLiteOpenHelper{privatestaticfinalStringTABLE="event";privatestaticfinalStringKEY_ID="id";privatestaticfinalStringKEY_TITLE="title";privatestaticfinalStringKEY_LOCATION="location";privatestaticfinalStringKEY_DAY_OF_WEEK="dayofweek";privatestaticfinalStringKEY_TOTAL_MINUTES_FROM="totalminutesfrom";privatestaticfinalStringKEY_TOTAL_MINUTES_TO="totalminutesto";publicEventTableHelper(Contextcontext){super(context,Constants.DATABASE_NAME,null,Constants.DATABASE_VERSION);//3rd argument to be passed is CursorFactory instance}// Creating Tables@OverridepublicvoidonCreate(SQLiteDatabasedb){//createTable(db);}publicvoidcreateTable(SQLiteDatabasedb){StringCREATE_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@OverridepublicvoidonUpgrade(SQLiteDatabasedb,intoldVersion,intnewVersion){// Drop older table if existed// db.execSQL("DROP TABLE IF EXISTS " + TABLE);// createTable(db);// Create tables again//onCreate(db);}// code to add the new contactpublicvoidaddEvent(EventDataevent){SQLiteDatabasedb=this.getWritableDatabase();ContentValuesvalues=newContentValues();values.put(KEY_ID,event.getId());values.put(KEY_TITLE,event.getTitle());// Contact Namevalues.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 Rowdb.insert(TABLE,null,values);//2nd argument is String containing nullColumnHackdb.close();// Closing database connection}// code to get the single contactEventDatagetEvent(intid){SQLiteDatabasedb=this.getReadableDatabase();Cursorcursor=db.query(TABLE,newString[]{KEY_ID,KEY_TITLE,KEY_DAY_OF_WEEK,KEY_TOTAL_MINUTES_FROM,KEY_TOTAL_MINUTES_TO,KEY_LOCATION},KEY_ID+"=?",newString[]{String.valueOf(id)},null,null,null,null);if(cursor!=null)cursor.moveToFirst();EventDataeventData=newEventData(Integer.parseInt(cursor.getString(0)),cursor.getString(1),cursor.getString(2),cursor.getInt(3),cursor.getInt(4),cursor.getString(5));returneventData;}// code to get all contacts in a list viewpublicListgetAllEvents(){ListconList=newArrayList();// Select All QueryStringselectQuery="SELECT * FROM "+TABLE;SQLiteDatabasedb=this.getWritableDatabase();Cursorcursor=db.rawQuery(selectQuery,null);// looping through all rows and adding to listif(cursor.moveToFirst()){do{EventDataevent=newEventData();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 listconList.add(event);}while(cursor.moveToNext());}// return contact listreturnconList;}}

How to solve this??

回答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 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.

回答2:

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.

回答3:

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

回答4:

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

回答5:

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)

回答6:

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