Java 唯一约束失败：sqlite 数据库：android

UNIQUE constraint failed: sqlite database : android

提问by

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 中出现错误。

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.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??

这个怎么解决？？

采纳答案by Mateusz Herych

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

您的代码可能违反了主键对KEY_ID字段的唯一性约束。

Two possible solutions are:

两种可能的解决方案是：

1. 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 idvalue.
2. If you don't care about generating ids by yourself, you can add AUTOINCREMENTsetting to your KEY_IDcolumn definition. This way KEY_IDfield will be filled automatically and each row will have its own, unique value. Once there, don't forget to remove adding KEY_IDto ContentValuesby yourself.

1. 确保您EventData.getId()为每个对象返回唯一值。目前，我没有看到您将任何标识符传递给其构造函数，而且可能所有事件都插入了相同的id值。
2. 如果您不关心自己生成 id，则可以AUTOINCREMENT在KEY_ID列定义中添加设置。这样KEY_ID字段将自动填充，每一行都有自己的唯一值。一旦出现，不要忘记删除添加KEY_ID到ContentValues自己。

回答by Gabe Sechan

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.

该表具有唯一约束。这意味着给定的 ID 值只能存在一行。如果您尝试更改某行的某些值，请使用 UPDATE 而不是 INSERT。如果您尝试添加此行，则需要为其指定不同的唯一 ID，或者需要先删除预先存在的行。以下哪个是正确答案取决于您的应用程序正在做什么。

回答by Kai Wang

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

尝试检查 ID 是否已存在。如果为 true，则不要插入，因为您已经拥有具有此 ID 的行。

回答by HannahCarney

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)

回答by Sam

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

我的错误是，我试图填充ID列，尽管它已经被定义为INTEGER PRIMARY KEY AUTOINCREMENT

回答by Vickie Kangare

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

使 id 列 id 整数自动递增，并且不要将 id 值放入内容值中。

回答by Dasser Basyouni

For developers using Room Persistence Library. You can use

对于使用Room Persistence Library 的开发人员。您可以使用

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

in DAO according to Insert Documentation

根据插入文档在 DAO 中

回答by Max Zonov

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

我有同样的问题，问题是，我忘了加db.execSQL(YOUR_TABLE);我DbHelper

回答by user155

If you use Roomthen instead of @PrimaryKeyyou should use @PrimaryKey(autoGenerate = true)

如果你使用Room那么@PrimaryKey你应该使用@PrimaryKey(autoGenerate = true)

and make your id variable optional:

并使您的 id 变量可选：

@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)

@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 nullas id, insertfunc will return new id, add it to your object

然后在添加新项目时，null作为 id传递，insertfunc 将返回新 id，将其添加到您的对象中

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